import traceback

import pymysql
import KBase
import re
import time
from pypinyin import pinyin, lazy_pinyin, Style
#补齐人名对应朝代，保存在tmp表
def adjustTable(tablename):
    truncate="truncate table tmp"
    cur1.execute(truncate)
    person_name="select `朝代`,`人名` from "+tablename
    cur1.execute(person_name)
    person_names=cur1.fetchall()
    insert="insert into tmp(`朝代`,`人名`) values(%s,%s)"
    datas=[]
    for data in person_names:
        dynasty=data[0]
        name=data[1]
        if dynasty:
            dy=dynasty.replace(' ','')
        datas.append((dy,name))
    cur1.executemany(insert,datas)
    conn1.commit()

#根据人名在人物表中获取所有的人物信息
def getPersonInfo():
    persons='select `朝代`,`人名` from tmp group by `朝代`,`人名`'
    cur1.execute(persons)
    persons=cur1.fetchall()
    names=[]
    values=[]
    for person in persons:
        names.append(person[1])
    # print(names)
    for name in names:
        sql="select c_personid from `人物基本表_简` where `c_name_chn`='"+name+"'"
        print(sql)
        cur2.execute(sql)
        dataforname=cur2.fetchall()#指定人名对应的c_personid集合，可能存在多个重名的人
        dataforname1=[]
        #如果人物基本表_简可以查到指定人名的数据
        if dataforname:
            for a in dataforname:
                id=str(a[0])
                dataforname1.append(id)#获取所有的c_personid
            dataforname1=','.join(dataforname1)
            sql1="select c_personid,c_status_code from  `关系_人物_社会地位_关系表` where `c_personid` in ("+dataforname1+") limit 1"
            cur2.execute(sql1)
            #获取personid对应的社会地位类型编号
            idandtype=cur2.fetchall()
            if idandtype:
                if len(idandtype)>1:
                    print(idandtype)
                for id in idandtype:
                    personid=id[0]
                    statuscode=id[1]
                    sql2="select c_personid,c_name,c_name_chn,c_female,c_birthyear,c_deathyear,c_ethnicity_code,c_index_year,c_household_status_code,c_tribe,c_by_nh_code,c_by_nh_year,c_by_range,c_dy_nh_code,c_dy_nh_year,c_dy_range,c_death_age,c_death_age_range,c_fl_earliest_year,c_fl_ey_nh_code,c_fl_ey_nh_year,c_fl_ey_notes,c_fl_latest_year,c_fl_ly_nh_code,c_fl_ly_nh_year,c_fl_ly_notes,c_surname,c_surname_chn,c_mingzi,c_mingzi_chn,c_choronym_code,c_notes,c_by_intercalary,c_dy_intercalary,c_by_month,c_dy_month,c_by_day,c_dy_day,c_by_day_gz,c_dy_day_gz,c_surname_proper,c_mingzi_proper,c_name_proper,c_surname_rm,c_mingzi_rm,c_name_rm,c_created_by,c_created_date,c_modified_by,c_modified_date,c_self_bio,c_dy  from `人物基本表_简` where c_personid="+str(personid)
                    #print(sql2)
                    cur2.execute(sql2)
                    data2=cur2.fetchall();data2=data2[0]
                    #print(data2)
                    sql3="select c_status_code,c_status_desc_chn  from `2社会地位基本表_简` where c_status_code="+str(int(statuscode))
                    #print(sql3)
                    cur2.execute(sql3)
                    data3=cur2.fetchall();data3=data3[0]
                    #print(data3)
                    tmp_data=[]
                    for d in data2:
                        if d:
                            tmp_data.append(d)
                        else:
                            tmp_data.append(None)
                    # sql4="select `朝代` from tmp where `人名`='"+name+"'"
                    # cur1.execute(sql4)
                    # person_name=cur1.fetchall()
                    # #print(person_name)
                    # person_name=person_name[0][0]
                    # tmp_data.append(person_name)
                    for b in data3:
                        tmp_data.append(b)
                    #print(tmp_data)
                    insert_sql='insert into `人物基本表_tmp`(c_personid,c_name,c_name_chn,c_female,c_birthyear,c_deathyear,c_ethnicity_code,c_index_year,c_household_status_code,c_tribe,c_by_nh_code,c_by_nh_year,c_by_range,c_dy_nh_code,c_dy_nh_year,c_dy_range,c_death_age,c_death_age_range,c_fl_earliest_year,c_fl_ey_nh_code,c_fl_ey_nh_year,c_fl_ey_notes,c_fl_latest_year,c_fl_ly_nh_code,c_fl_ly_nh_year,c_fl_ly_notes,c_surname,c_surname_chn,c_mingzi,c_mingzi_chn,c_choronym_code,c_notes,c_by_intercalary,c_dy_intercalary,c_by_month,c_dy_month,c_by_day,c_dy_day,c_by_day_gz,c_dy_day_gz,c_surname_proper,c_mingzi_proper,c_name_proper,c_surname_rm,c_mingzi_rm,c_name_rm,c_created_by,c_created_date,c_modified_by,c_modified_date,c_self_bio,c_dy,c_status_code,c_status_desc_chn) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
                    #print(insert_sql)
                    cur2.execute(insert_sql,tmp_data)
                    conn2.commit()
            else:
                sql2="select c_personid,c_name,c_name_chn,c_female,c_birthyear,c_deathyear,c_ethnicity_code,c_index_year,c_household_status_code,c_tribe,c_by_nh_code,c_by_nh_year,c_by_range,c_dy_nh_code,c_dy_nh_year,c_dy_range,c_death_age,c_death_age_range,c_fl_earliest_year,c_fl_ey_nh_code,c_fl_ey_nh_year,c_fl_ey_notes,c_fl_latest_year,c_fl_ly_nh_code,c_fl_ly_nh_year,c_fl_ly_notes,c_surname,c_surname_chn,c_mingzi,c_mingzi_chn,c_choronym_code,c_notes,c_by_intercalary,c_dy_intercalary,c_by_month,c_dy_month,c_by_day,c_dy_day,c_by_day_gz,c_dy_day_gz,c_surname_proper,c_mingzi_proper,c_name_proper,c_surname_rm,c_mingzi_rm,c_name_rm,c_created_by,c_created_date,c_modified_by,c_modified_date,c_self_bio,c_dy  from `人物基本表_简` where c_personid="+id
                cur2.execute(sql2)
                data2=cur2.fetchall();data2=data2[0]
                tmp_data=[]
                for d in data2:
                    if d:
                        tmp_data.append(d)
                    else:
                        tmp_data.append(None)
                # sql4="select `朝代` from tmp where `人名`='"+name+"'"
                # cur1.execute(sql4)
                # person_name=cur1.fetchall()
                # #print(person_name)
                # person_name=person_name[0][0]
                # tmp_data.append(person_name)
                insert_sql='insert into `人物基本表_tmp`(c_personid,c_name,c_name_chn,c_female,c_birthyear,c_deathyear,c_ethnicity_code,c_index_year,c_household_status_code,c_tribe,c_by_nh_code,c_by_nh_year,c_by_range,c_dy_nh_code,c_dy_nh_year,c_dy_range,c_death_age,c_death_age_range,c_fl_earliest_year,c_fl_ey_nh_code,c_fl_ey_nh_year,c_fl_ey_notes,c_fl_latest_year,c_fl_ly_nh_code,c_fl_ly_nh_year,c_fl_ly_notes,c_surname,c_surname_chn,c_mingzi,c_mingzi_chn,c_choronym_code,c_notes,c_by_intercalary,c_dy_intercalary,c_by_month,c_dy_month,c_by_day,c_dy_day,c_by_day_gz,c_dy_day_gz,c_surname_proper,c_mingzi_proper,c_name_proper,c_surname_rm,c_mingzi_rm,c_name_rm,c_created_by,c_created_date,c_modified_by,c_modified_date,c_self_bio,c_dy) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
                #print(insert_sql)
                cur2.execute(insert_sql,tmp_data)
                conn2.commit()
        else:
            #into="insert into `人物基本表_new_1`(c_name_chn,c_dynasty_chn) values(%s,%s)"
            sql4="select `朝代` from tmp where `人名`='"+name+"'"
            cur1.execute(sql4)
            dynasty=cur1.fetchall()
            #print(dynasty)
            dynasty=dynasty[0][0]
            value=(name,dynasty)
            values.append(value)
    into="insert into `人物基本表_tmp`(c_name_chn,c_dynasty_chn) values(%s,%s)"
    cur2.executemany(into,values)
    conn2.commit()

def getPersonInfo2(table_name):
    persons='select `朝代`,`人名` from tmp group by `朝代`,`人名`'
    cur1.execute(persons)
    persons=cur1.fetchall()
    names=[]
    values=[]
    for person in persons:
        dynasty=person[0]
        name=person[1]
        sql="select c_personid,c_name_chn,c_dynasty_chn from `人物基本表_简` where `c_name_chn`='"+name+"' and c_dynasty_chn is not null group by c_name_chn,c_dynasty_chn"
        print(sql)
        cur2.execute(sql)
        dataforname=cur2.fetchall()#指定人名对应的多条数据集合
        dataforname1=[]
        #如果人物基本表_简可以查到指定人名的数据
        aa=[]
        if dataforname:
            flag=False
            for a in dataforname:
                t_dynasty=None
                personid=str(a[0])
                c_name=a[1]
                c_dynasty=a[2]
                if '三国' in c_dynasty:
                    t_dynasty='三国'
                elif '晋' in c_dynasty:
                    t_dynasty='晋'
                elif '秦' in c_dynasty:
                    t_dynasty='秦'
                else:
                    t_dynasty=c_dynasty
                if t_dynasty in dynasty:#如果人物基本表_简中的朝代和人物朝代对应
                    flag=True
                    sel_sql="select c_personid,c_status_code from  `关系_人物_社会地位_关系表` where `c_personid`="+str(personid)+" limit 1"
                    cur2.execute(sel_sql)
                    #获取personid对应的社会地位类型编号
                    idandtype=cur2.fetchall()
                    if idandtype:
                        statuscode=idandtype[0][1]
                        sql2="select c_personid,c_name,c_name_chn,c_female,c_birthyear,c_deathyear,c_ethnicity_code,c_index_year,c_household_status_code,c_tribe,c_by_nh_code,c_by_nh_year,c_by_range,c_dy_nh_code,c_dy_nh_year,c_dy_range,c_death_age,c_death_age_range,c_fl_earliest_year,c_fl_ey_nh_code,c_fl_ey_nh_year,c_fl_ey_notes,c_fl_latest_year,c_fl_ly_nh_code,c_fl_ly_nh_year,c_fl_ly_notes,c_surname,c_surname_chn,c_mingzi,c_mingzi_chn,c_choronym_code,c_notes,c_by_intercalary,c_dy_intercalary,c_by_month,c_dy_month,c_by_day,c_dy_day,c_by_day_gz,c_dy_day_gz,c_surname_proper,c_mingzi_proper,c_name_proper,c_surname_rm,c_mingzi_rm,c_name_rm,c_created_by,c_created_date,c_modified_by,c_modified_date,c_self_bio,c_dy,c_dynasty_chn  from `人物基本表_简` where c_personid="+str(personid)
                        cur2.execute(sql2)
                        data2=cur2.fetchall();data2=data2[0]
                        print(len(data2))
                        print(data2)
                        sql3="select c_status_code,c_status_desc_chn  from `2社会地位基本表_简` where c_status_code="+str(int(statuscode))
                        cur2.execute(sql3)
                        data3=cur2.fetchall();data3=data3[0]
                        print(len(data3))
                        print(data3)
                        insert_sql='insert into `人物基本表_tmp`(c_personid,c_name,c_name_chn,c_female,c_birthyear,c_deathyear,c_ethnicity_code,c_index_year,c_household_status_code,c_tribe,c_by_nh_code,c_by_nh_year,c_by_range,c_dy_nh_code,c_dy_nh_year,c_dy_range,c_death_age,c_death_age_range,c_fl_earliest_year,c_fl_ey_nh_code,c_fl_ey_nh_year,c_fl_ey_notes,c_fl_latest_year,c_fl_ly_nh_code,c_fl_ly_nh_year,c_fl_ly_notes,c_surname,c_surname_chn,c_mingzi,c_mingzi_chn,c_choronym_code,c_notes,c_by_intercalary,c_dy_intercalary,c_by_month,c_dy_month,c_by_day,c_dy_day,c_by_day_gz,c_dy_day_gz,c_surname_proper,c_mingzi_proper,c_name_proper,c_surname_rm,c_mingzi_rm,c_name_rm,c_created_by,c_created_date,c_modified_by,c_modified_date,c_self_bio,c_dy,c_dynasty_chn,c_status_code,c_status_desc_chn,tablename) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
                        tmp_data=[]
                        for d in data2:
                            if d:
                                tmp_data.append(d)
                            else:
                                tmp_data.append(None)
                        for b in data3:
                            tmp_data.append(b)
                        #print(insert_sql)
                        tmp_data.append(table_name)
                        cur2.execute(insert_sql,tmp_data)
                        conn2.commit()
                    else:
                        sql2="select c_personid,c_name,c_name_chn,c_female,c_birthyear,c_deathyear,c_ethnicity_code,c_index_year,c_household_status_code,c_tribe,c_by_nh_code,c_by_nh_year,c_by_range,c_dy_nh_code,c_dy_nh_year,c_dy_range,c_death_age,c_death_age_range,c_fl_earliest_year,c_fl_ey_nh_code,c_fl_ey_nh_year,c_fl_ey_notes,c_fl_latest_year,c_fl_ly_nh_code,c_fl_ly_nh_year,c_fl_ly_notes,c_surname,c_surname_chn,c_mingzi,c_mingzi_chn,c_choronym_code,c_notes,c_by_intercalary,c_dy_intercalary,c_by_month,c_dy_month,c_by_day,c_dy_day,c_by_day_gz,c_dy_day_gz,c_surname_proper,c_mingzi_proper,c_name_proper,c_surname_rm,c_mingzi_rm,c_name_rm,c_created_by,c_created_date,c_modified_by,c_modified_date,c_self_bio,c_dy,c_dynasty_chn  from `人物基本表_简` where c_personid="+str(personid)
                        cur2.execute(sql2)
                        data2=cur2.fetchall();data2=data2[0]
                        tmp_data=[]
                        for d in data2:
                            if d:
                                tmp_data.append(d)
                            else:
                                tmp_data.append(None)

                        tmp_data.append(table_name)
                        insert_sql='insert into `人物基本表_tmp`(c_personid,c_name,c_name_chn,c_female,c_birthyear,c_deathyear,c_ethnicity_code,c_index_year,c_household_status_code,c_tribe,c_by_nh_code,c_by_nh_year,c_by_range,c_dy_nh_code,c_dy_nh_year,c_dy_range,c_death_age,c_death_age_range,c_fl_earliest_year,c_fl_ey_nh_code,c_fl_ey_nh_year,c_fl_ey_notes,c_fl_latest_year,c_fl_ly_nh_code,c_fl_ly_nh_year,c_fl_ly_notes,c_surname,c_surname_chn,c_mingzi,c_mingzi_chn,c_choronym_code,c_notes,c_by_intercalary,c_dy_intercalary,c_by_month,c_dy_month,c_by_day,c_dy_day,c_by_day_gz,c_dy_day_gz,c_surname_proper,c_mingzi_proper,c_name_proper,c_surname_rm,c_mingzi_rm,c_name_rm,c_created_by,c_created_date,c_modified_by,c_modified_date,c_self_bio,c_dy,c_dynasty_chn,tablename) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
                        cur2.execute(insert_sql,tmp_data)
                        conn2.commit()
            if flag==False:
                value=(name,dynasty,table_name)
                values.append(value)

        else:
            sql_a="select c_personid,c_name_chn,c_dynasty_chn from `人物基本表_简` where `c_name_chn`='"+name+"' and c_dynasty_chn is null group by c_name_chn,c_dynasty_chn"
            print(sql_a)
            cur2.execute(sql)
            dataforname_a=cur2.fetchall()
            if dataforname_a:
                personid_a=str(dataforname_a[0])
                c_name_a=dataforname_a[1]
                c_dynasty_a=dataforname_a[2]
                sel_sql="select c_personid,c_status_code from  `关系_人物_社会地位_关系表` where `c_personid`="+str(personid_a)+" limit 1"
                cur2.execute(sel_sql)
                #获取personid对应的社会地位类型编号
                idandtype=cur2.fetchall()
                if idandtype:
                    statuscode=idandtype[0][1]
                    sql2="select c_personid,c_name,c_name_chn,c_female,c_birthyear,c_deathyear,c_ethnicity_code,c_index_year,c_household_status_code,c_tribe,c_by_nh_code,c_by_nh_year,c_by_range,c_dy_nh_code,c_dy_nh_year,c_dy_range,c_death_age,c_death_age_range,c_fl_earliest_year,c_fl_ey_nh_code,c_fl_ey_nh_year,c_fl_ey_notes,c_fl_latest_year,c_fl_ly_nh_code,c_fl_ly_nh_year,c_fl_ly_notes,c_surname,c_surname_chn,c_mingzi,c_mingzi_chn,c_choronym_code,c_notes,c_by_intercalary,c_dy_intercalary,c_by_month,c_dy_month,c_by_day,c_dy_day,c_by_day_gz,c_dy_day_gz,c_surname_proper,c_mingzi_proper,c_name_proper,c_surname_rm,c_mingzi_rm,c_name_rm,c_created_by,c_created_date,c_modified_by,c_modified_date,c_self_bio  from `人物基本表_简` where c_personid="+str(personid)
                    cur2.execute(sql2)
                    data2=cur2.fetchall();data2=data2[0]
                    print(len(data2))
                    print(data2)
                    sql3="select c_status_code,c_status_desc_chn  from `2社会地位基本表_简` where c_status_code="+str(int(statuscode))
                    cur2.execute(sql3)
                    data3=cur2.fetchall();data3=data3[0]
                    print(len(data3))
                    print(data3)
                    insert_sql='insert into `人物基本表_tmp`(c_personid,c_name,c_name_chn,c_female,c_birthyear,c_deathyear,c_ethnicity_code,c_index_year,c_household_status_code,c_tribe,c_by_nh_code,c_by_nh_year,c_by_range,c_dy_nh_code,c_dy_nh_year,c_dy_range,c_death_age,c_death_age_range,c_fl_earliest_year,c_fl_ey_nh_code,c_fl_ey_nh_year,c_fl_ey_notes,c_fl_latest_year,c_fl_ly_nh_code,c_fl_ly_nh_year,c_fl_ly_notes,c_surname,c_surname_chn,c_mingzi,c_mingzi_chn,c_choronym_code,c_notes,c_by_intercalary,c_dy_intercalary,c_by_month,c_dy_month,c_by_day,c_dy_day,c_by_day_gz,c_dy_day_gz,c_surname_proper,c_mingzi_proper,c_name_proper,c_surname_rm,c_mingzi_rm,c_name_rm,c_created_by,c_created_date,c_modified_by,c_modified_date,c_self_bio,c_status_code,c_status_desc_chn,tablename,c_dynasty_chn) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
                    tmp_data=[]
                    for d in data2:
                        if d:
                            tmp_data.append(d)
                        else:
                            tmp_data.append(None)
                    for b in data3:
                        tmp_data.append(b)
                    #print(insert_sql)
                    tmp_data.append(table_name)
                    tmp_data.append(dynasty)
                    cur2.execute(insert_sql,tmp_data)
                    conn2.commit()
                else:
                    sql2="select c_personid,c_name,c_name_chn,c_female,c_birthyear,c_deathyear,c_ethnicity_code,c_index_year,c_household_status_code,c_tribe,c_by_nh_code,c_by_nh_year,c_by_range,c_dy_nh_code,c_dy_nh_year,c_dy_range,c_death_age,c_death_age_range,c_fl_earliest_year,c_fl_ey_nh_code,c_fl_ey_nh_year,c_fl_ey_notes,c_fl_latest_year,c_fl_ly_nh_code,c_fl_ly_nh_year,c_fl_ly_notes,c_surname,c_surname_chn,c_mingzi,c_mingzi_chn,c_choronym_code,c_notes,c_by_intercalary,c_dy_intercalary,c_by_month,c_dy_month,c_by_day,c_dy_day,c_by_day_gz,c_dy_day_gz,c_surname_proper,c_mingzi_proper,c_name_proper,c_surname_rm,c_mingzi_rm,c_name_rm,c_created_by,c_created_date,c_modified_by,c_modified_date,c_self_bio  from `人物基本表_简` where c_personid="+str(personid)
                    cur2.execute(sql2)
                    data2=cur2.fetchall();data2=data2[0]
                    tmp_data=[]
                    for d in data2:
                        if d:
                            tmp_data.append(d)
                        else:
                            tmp_data.append(None)

                    tmp_data.append(table_name)
                    tmp_data.append(dynasty)
                    insert_sql='insert into `人物基本表_tmp`(c_personid,c_name,c_name_chn,c_female,c_birthyear,c_deathyear,c_ethnicity_code,c_index_year,c_household_status_code,c_tribe,c_by_nh_code,c_by_nh_year,c_by_range,c_dy_nh_code,c_dy_nh_year,c_dy_range,c_death_age,c_death_age_range,c_fl_earliest_year,c_fl_ey_nh_code,c_fl_ey_nh_year,c_fl_ey_notes,c_fl_latest_year,c_fl_ly_nh_code,c_fl_ly_nh_year,c_fl_ly_notes,c_surname,c_surname_chn,c_mingzi,c_mingzi_chn,c_choronym_code,c_notes,c_by_intercalary,c_dy_intercalary,c_by_month,c_dy_month,c_by_day,c_dy_day,c_by_day_gz,c_dy_day_gz,c_surname_proper,c_mingzi_proper,c_name_proper,c_surname_rm,c_mingzi_rm,c_name_rm,c_created_by,c_created_date,c_modified_by,c_modified_date,c_self_bio,tablename,c_dynasty_chn) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
                    cur2.execute(insert_sql,tmp_data)
                    conn2.commit()
            else:
                value=(name,dynasty,table_name)
                values.append(value)
    into="insert into `人物基本表_tmp`(c_name_chn,c_dynasty_chn,tablename) values(%s,%s,%s)"
    cur2.executemany(into,values)
    conn2.commit()

def getGJSInfo():
    sql = "select tts_sysno,c_name_chn,c_dynasty_chn,c_personid from 人物基本表_new where gjs_book is null and isdelete=0 and c_dynasty_chn in ('前燕','后秦','南燕','前凉','西凉','北燕','北凉','后燕','后赵','前秦','前赵','成汉','夏朝','西秦','后凉','南凉','西燕')"
    cur2.execute(sql)
    persons=cur2.fetchall()
    con=0

    for person in persons:
        try:
            sysno=person[0]
            name=person[1]
            dynasty=person[2]
            personid=person[3]
            t_dynasty=None
            #kbase_sql="SELECT AFTER5_ENTRY,BEFORE5_ENTRY,BOOK,BOOK_ID,CODE,ENTRY_11,ENTRY_13,ENTRY_2,ENTRY_CLASS,ENTRY_ID,PARENT_NODE,PUB_DATE,SYS_NVSM_K,SYS_VSM FROM CRFD2008,CRFD2009,CRFD2010,CRFD2011,CRFD2012,CRFD2013,CRFD2014,CRFD2015,CRFD2016,CRFD2017,CRFD2018,CRFD2019,CRFD2020 where ENTRY_11='"+name+"' and ENTRY_2 % '"+dynasty+"'"
            kbase_sql="SELECT AFTER5_ENTRY,BEFORE5_ENTRY,BOOK,BOOK_ID,CODE,ENTRY_11,ENTRY_13,ENTRY_2,ENTRY_CLASS,ENTRY_ID,PARENT_NODE,PUB_DATE,SYS_NVSM_K,SYS_VSM FROM CRFD2008,CRFD2009,CRFD2010,CRFD2011,CRFD2012,CRFD2013,CRFD2014,CRFD2015,CRFD2016,CRFD2017,CRFD2018,CRFD2019,CRFD2020 where ENTRY_11='"+name+"' and ENTRY_2 % '十六国'"
            print(kbase_sql)
            cur_kbase.execute(kbase_sql,bunicode=True)
            books_info=cur_kbase.fetchall()

            if books_info:
                #print(len(books_info))
                onedata=books_info[0]#获取每个人的第一条数据
                #下面集合的下标需要根据实际调整
                gjs_bookid=onedata[3]
                gjs_book=onedata[2]
                gjs_name=onedata[5]
                gjs_txt=onedata[7].strip()
                gjs_time=onedata[11]
                gjs_keyword=onedata[12]
                gjs_vsm=onedata[13]
                gjs_entry_id=onedata[9]
                gjs_url="https://gongjushu.cnki.net/RBook/Detail?entryId="+gjs_entry_id
                gjs_before=onedata[1]
                gjs_after=onedata[0]
                gjs_code=onedata[4]
                gjs_parent_node=onedata[10]
                gjs_entry_class=onedata[8]
                gjs_person_relation=onedata[0]
                gjs_addr_relation=onedata[0]
                gjs_time_relation=onedata[0]
                gjs_org_relation=onedata[0]
                gjs_dy_relation=onedata[0]
                gjs_count=len(books_info)
                gjs_entries=onedata[0]
                bdyear=''

                #向人物工具书表_new添加工具书数据
                for book in books_info:
                    flag=False
                    #下面集合的下标需要根据实际调整，看ENTRY_13是第几个字段
                    year=book[6]
                    #print(year)
                    if flag==False:
                        if year:
                            bdyear=year
                            flag=True
                    datas=[]

                    #下面集合的下标需要根据实际调整
                    gjs_bookid=book[3]
                    gjs_book=book[2]
                    gjs_entry_id=book[9]
                    data=(sysno,personid,name,gjs_bookid,gjs_book,gjs_entry_id)
                    datas.append(data)
                    into_sql="insert into `人物工具书表_new`(tts_sysno,c_personid,c_name_chn,gjs_bookid,gjs_book,gjs_entry_id) values(%s,%s,%s,%s,%s,%s)"
                    cur2.executemany(into_sql,datas)
                    conn2.commit()
                #更新人物基本表_tmp中的缺失字段
                #print('bdyear='+bdyear)
                if "—" in bdyear:
                    birth_year=bdyear.split('—')[0]#出生年份
                    death_year=bdyear.split('—')[1]#去世年份
                    update_sql="update `人物基本表_new` set gjs_bookid='"+gjs_bookid+"',gjs_book='"+gjs_book+"',gjs_name='"+gjs_name+"',gjs_txt='"+gjs_txt+"',gjs_time='"+gjs_time+"',gjs_keyword='"+gjs_keyword+"',gjs_vsm='"+gjs_vsm+"',gjs_entry_id='"+gjs_entry_id+"',gjs_url='"+gjs_url+"',gjs_before='"+gjs_before+"',gjs_after='"+gjs_after+"',gjs_code='"+gjs_code+"',gjs_parent_node='"+gjs_parent_node+"',gjs_entry_class='"+gjs_entry_class+"',gjs_count="+str(gjs_count)+",c_birthyear='"+birth_year+"',c_deathyear='"+death_year+"',c_by_to_death='"+bdyear+"',isnewinfo=1 where c_name_chn='"+name+"' and tts_sysno="+str(sysno)
                else:
                    #update_sql="update `人物基本表_tmp` set gjs_bookid='"+gjs_bookid+"',gjs_book='"+gjs_book+"',gjs_name='"+gjs_name+"',gjs_txt='"+gjs_txt+"',gjs_time='"+gjs_time+"',gjs_keyword='"+gjs_keyword+"',gjs_vsm='"+gjs_vsm+"',gjs_entry_id='"+gjs_entry_id+"',gjs_url='"+gjs_url+"',gjs_before='"+gjs_before+"',gjs_after='"+gjs_after+"',gjs_code='"+gjs_code+"',gjs_parent_node='"+gjs_parent_node+"',gjs_entry_class='"+gjs_entry_class+"',gjs_person_relation='"+gjs_person_relation+"',gjs_addr_relation='"+gjs_addr_relation+"',gjs_time_relation='"+gjs_time_relation+"',gjs_org_relation='"+gjs_org_relation+"',gjs_dy_relation='"+gjs_dy_relation+"',gjs_count='"+str(gjs_count)+"',gjs_entries='"+gjs_entries+"',c_birthyear="+str(birth_year)+"',c_deathyear="+str(death_year)+"',c_by_to_death='"+bdyear+" where c_name_chn='"+name+"' and tts_sysno="+str(sysno)
                    update_sql="update `人物基本表_new` set gjs_bookid='"+gjs_bookid+"',gjs_book='"+gjs_book+"',gjs_name='"+gjs_name+"',gjs_txt='"+gjs_txt+"',gjs_time='"+gjs_time+"',gjs_keyword='"+gjs_keyword+"',gjs_vsm='"+gjs_vsm+"',gjs_entry_id='"+gjs_entry_id+"',gjs_url='"+gjs_url+"',gjs_before='"+gjs_before+"',gjs_after='"+gjs_after+"',gjs_code='"+gjs_code+"',gjs_parent_node='"+gjs_parent_node+"',gjs_entry_class='"+gjs_entry_class+"',gjs_count="+str(gjs_count)+",c_by_to_death='"+bdyear+"',isnewinfo=1 where c_name_chn='"+name+"' and tts_sysno="+str(sysno)
                print(update_sql)
                con=con+1
                cur2.execute(update_sql)
                conn2.commit()
        except:
            print("异常人物名："+name)
            continue
    print("共新增"+str(con)+"条")

def adddynasty():
    sql="select tts_sysno,c_personid,c_name_chn from `人物基本表_new` where  gjs_book is null and tts_sysno>=16555 and c_dynasty_chn is null"
    cur2.execute(sql)
    persons=cur2.fetchall()
    for person in persons:
        tts_sysno=person[0]
        c_personid=person[1]
        c_name_chn=person[2]
        sql1="select c_name_chn,c_dy from `人物基本表_简` where c_name_chn='"+c_name_chn+"' and c_personid="+str(c_personid)
        cur2.execute(sql1)
        dys=cur2.fetchall()
        if dys:
            for dy in dys :
                name=dy[0]
                dy_code=dy[1]
                if dy_code:
                    if dy_code>0:
                        update_sql="update `人物基本表_new` set c_dy="+str(dy_code)+" where c_name_chn='"+c_name_chn+"' and c_personid="+str(c_personid)
                        print(update_sql)

def officeAdjust():
    sql="select * from `官职表` where   c_office_id is not null and c_parent_id like '16%'"
    cur2.execute(sql)
    datas=cur2.fetchall()
    num=50
    id=None
    for data in datas:
        office_id=data[2]
        office_name=data[5]
        parent_id=data[7]
        if id ==None:
            id=parent_id
        if id==parent_id:
            node_id=id+str(num)
            num=num+1
        else:
            num=50
            id=parent_id
            node_id=id+str(num)
            num=num+1
        update="update `官职表` set c_office_type_node_id ='"+node_id+"' where c_office_id="+str(office_id)+" and c_office_type_desc_chn='"+office_name+"'"
        print(update)
        cur2.execute(update)
        conn2.commit()

def adjusttang():
    sql="select * from `官职表_copy` where c_office_id is not null and c_parent_id like '06%' and  LENGTH(c_parent_id)>=6 "
    cur2.execute(sql)
    datas=cur2.fetchall()
    for data in datas:
        print(data)
        node_id=data[0]
        office_id=data[1]
        office_name=data[3]
        parent_name=data[4]
        parent_id=data[5]
        if parent_name.endswith("门"):
            parent_name=parent_name.replace('门','')
            office_name=office_name.replace(parent_name,'')
        else:
            office_name=office_name.replace(parent_name,'')
        update="update `官职表_copy` set c_office_type_desc_chn='"+office_name+"' where c_office_type_node_id='"+node_id+"'"
        cur2.execute(update)
        conn2.commit()

def getOfficeBook():
    sql = "select sysno,c_office_type_node_id,c_office_id,c_dynasty_chn,c_office_type_desc_chn,c_period from `官职表_new` where  gjs_book is  null and c_parent_id!=0  and c_parent_id is not null  and c_dynasty_chn in ('春秋','战国') and c_office_type_node_id!='99010107'"
    cur2.execute(sql)
    offices=cur2.fetchall()
    names=[]
    for office in offices:
        sysno=office[0]
        node_id=office[1]
        office_id=office[2]
        dynasty=office[3]
        office_name=office[4]
        period=office[5]
        kbase_sql="SELECT AFTER5_ENTRY,BEFORE5_ENTRY,BOOK,BOOK_ID,CODE,ENTRY_11,ENTRY_13,ENTRY_2,ENTRY_CLASS,ENTRY_ID,PARENT_NODE,PUB_DATE,SYS_NVSM_K,SYS_VSM FROM CRFD2008,CRFD2009,CRFD2010,CRFD2011,CRFD2012,CRFD2013,CRFD2014,CRFD2015,CRFD2016,CRFD2017,CRFD2018,CRFD2019,CRFD2020 where ENTRY_11='"+office_name+"' and  ENTRY_2 % '"+dynasty+"'"
        print(kbase_sql)
        cur_kbase.execute(kbase_sql,bunicode=True)
        office_info=cur_kbase.fetchall()
        if office_info:
            onedata=office_info[0]#获取每个官职的第一条数据
            #下面集合的下标需要根据实际调整
            gjs_bookid=onedata[3]
            gjs_book=onedata[2]
            gjs_name=onedata[5]
            gjs_txt=onedata[7].strip()
            gjs_time=onedata[11]
            gjs_keyword=onedata[12]
            gjs_vsm=onedata[13]
            gjs_entry_id=onedata[9]
            gjs_url="https://gongjushu.cnki.net/RBook/Detail?entryId="+gjs_entry_id
            gjs_before=onedata[1]
            gjs_after=onedata[0]
            gjs_code=onedata[4]
            gjs_parent_node=onedata[10]
            gjs_entry_class=onedata[8]
            gjs_person_relation=onedata[0]
            gjs_addr_relation=onedata[0]
            gjs_time_relation=onedata[0]
            gjs_org_relation=onedata[0]
            gjs_dy_relation=onedata[0]
            gjs_count=len(office_info)
            gjs_entries=onedata[0]
            #向官职工具书表_new添加工具书数据
            # for book in office_info:
            #     datas=[]
            #     #下面集合的下标需要根据实际调整
            #     gjs_bookid=book[3]
            #     gjs_book=book[2]
            #     gjs_entry_id=book[9]
            #     data=(sysno,office_id,office_name,gjs_bookid,gjs_book,gjs_entry_id)
            #     datas.append(data)
            #     into_sql="insert into `官职工具书表_new`(sysno,c_office_id,c_office_type_desc_chn,gjs_bookid,gjs_book,gjs_entry_id) values(%s,%s,%s,%s,%s,%s)"
            #     cur2.executemany(into_sql,datas)
            #     conn2.commit()
            #更新人物基本表_tmp中的缺失字段
            #update_sql="update `人物基本表_tmp` set gjs_bookid='"+gjs_bookid+"',gjs_book='"+gjs_book+"',gjs_name='"+gjs_name+"',gjs_txt='"+gjs_txt+"',gjs_time='"+gjs_time+"',gjs_keyword='"+gjs_keyword+"',gjs_vsm='"+gjs_vsm+"',gjs_entry_id='"+gjs_entry_id+"',gjs_url='"+gjs_url+"',gjs_before='"+gjs_before+"',gjs_after='"+gjs_after+"',gjs_code='"+gjs_code+"',gjs_parent_node='"+gjs_parent_node+"',gjs_entry_class='"+gjs_entry_class+"',gjs_person_relation='"+gjs_person_relation+"',gjs_addr_relation='"+gjs_addr_relation+"',gjs_time_relation='"+gjs_time_relation+"',gjs_org_relation='"+gjs_org_relation+"',gjs_dy_relation='"+gjs_dy_relation+"',gjs_count='"+str(gjs_count)+"',gjs_entries='"+gjs_entries+"',c_birthyear="+str(birth_year)+"',c_deathyear="+str(death_year)+"',c_by_to_death='"+bdyear+" where c_name_chn='"+name+"' and tts_sysno="+str(sysno)
            update_sql="update `官职表_new` set gjs_bookid='"+gjs_bookid+"',gjs_book='"+gjs_book+"',gjs_name='"+gjs_name+"',gjs_txt='"+gjs_txt+"',gjs_time='"+gjs_time+"',gjs_keyword='"+gjs_keyword+"',gjs_vsm='"+gjs_vsm+"',gjs_entry_id='"+gjs_entry_id+"',gjs_url='"+gjs_url+"',gjs_before='"+gjs_before+"',gjs_after='"+gjs_after+"',gjs_code='"+gjs_code+"',gjs_parent_node='"+gjs_parent_node+"',gjs_entry_class='"+gjs_entry_class+"',gjs_count="+str(gjs_count)+" where c_office_type_node_id='"+node_id+"' and c_office_type_desc_chn='"+office_name+"'"
            print(update_sql)
            cur2.execute(update_sql)
            conn2.commit()

def getOfficeByDynasty(dynasty):
    #kbase_sql="SELECT AFTER5_ENTRY,BEFORE5_ENTRY,BOOK,BOOK_ID,CODE,ENTRY_11,ENTRY_13,ENTRY_2,ENTRY_CLASS,ENTRY_ID,PARENT_NODE,PUB_DATE,SYS_NVSM_K,SYS_VSM FROM CRFD2008,CRFD2009,CRFD2010,CRFD2011,CRFD2012,CRFD2013,CRFD2014,CRFD2015,CRFD2016,CRFD2017,CRFD2018,CRFD2019,CRFD2020 where BOOK='中国历代官制大辞典' and (ENTRY_2 % '"+dynasty+"朝' or ENTRY_2 % '"+dynasty+"代')"
    kbase_sql="SELECT AFTER5_ENTRY,BEFORE5_ENTRY,BOOK,BOOK_ID,CODE,ENTRY_11,ENTRY_13,ENTRY_2,ENTRY_CLASS,ENTRY_ID,PARENT_NODE,PUB_DATE,SYS_NVSM_K,SYS_VSM FROM CRFD2008,CRFD2009,CRFD2010,CRFD2011,CRFD2012,CRFD2013,CRFD2014,CRFD2015,CRFD2016,CRFD2017,CRFD2018,CRFD2019,CRFD2020 where BOOK='中国历代官制大辞典' and ENTRY_2 % '"+dynasty+"国'"
    #kbase_sql="SELECT AFTER5_ENTRY,BEFORE5_ENTRY,BOOK,BOOK_ID,CODE,ENTRY_11,ENTRY_13,ENTRY_2,ENTRY_CLASS,ENTRY_ID,PARENT_NODE,PUB_DATE,SYS_NVSM_K,SYS_VSM FROM CRFD2008,CRFD2009,CRFD2010,CRFD2011,CRFD2012,CRFD2013,CRFD2014,CRFD2015,CRFD2016,CRFD2017,CRFD2018,CRFD2019,CRFD2020 where BOOK='中国历代官制大辞典' and  (ENTRY_2 % '秦朝' or ENTRY_2 % '秦代' or ENTRY_2 % '秦国')"
    print(kbase_sql)
    cur_kbase.execute(kbase_sql,bunicode=True)
    offices=cur_kbase.fetchall()
    print(len(offices))
    if offices:
        for office in offices:
            gjs_bookid=office[3]
            gjs_book=office[2]
            gjs_name=office[5]
            gjs_txt=office[7].strip()
            gjs_time=office[11]
            gjs_keyword=office[12]
            gjs_vsm=office[13]
            gjs_entry_id=office[9]
            gjs_url="https://gongjushu.cnki.net/RBook/Detail?entryId="+gjs_entry_id
            gjs_before=office[1]
            gjs_after=office[0]
            gjs_code=office[4]
            gjs_parent_node=office[10]
            gjs_entry_class=office[8]
            gjs_person_relation=office[0]
            gjs_addr_relation=office[0]
            gjs_time_relation=office[0]
            gjs_org_relation=office[0]
            gjs_dy_relation=office[0]
            gjs_entries=office[0]
            insert_sql="insert into `官职表`(c_dynasty_chn,c_office_type_desc_chn,gjs_bookid,gjs_book,gjs_name,gjs_txt,gjs_time,gjs_keyword,gjs_vsm,gjs_entry_id,gjs_url,gjs_before,gjs_after,gjs_code,gjs_parent_node,gjs_entry_class) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            data=(dynasty,gjs_name,gjs_bookid,gjs_book,gjs_name,gjs_txt,gjs_time,gjs_keyword,gjs_vsm,gjs_entry_id,gjs_url,gjs_before,gjs_after,gjs_code,gjs_parent_node,gjs_entry_class)
            cur2.execute(insert_sql,data)
            conn2.commit()

def adjustliao():
    sql1="select c_parent_name from 官职表 where sysno>=35131 and sysno<=35862 and c_parent_id like '16%' and c_parent_name like '%部\ufeff' group by c_parent_name"
    cur2.execute(sql1)
    all=cur2.fetchall()
    print(all)
    for i in all:
        i=i[0].replace('\ufeff','')
        # if '大王府' in i:
        #     up=i.split('大王府')[0]
        #     sql="update 官职表 set c_office_type_desc_chn=REPLACE(c_office_type_desc_chn,'"+up+"','') where c_parent_id like '16%' and  c_office_type_desc_chn like '"+up+"%'"
        #     print(sql)
        # elif '王府' in i:
        #     up=i.split('王府')[0]
        #     sql="update 官职表 set c_office_type_desc_chn=REPLACE(c_office_type_desc_chn,'"+up+"','') where c_parent_id like '16%' and  c_office_type_desc_chn like '"+up+"%'"
        #     print(sql)
        sql="update 官职表 set c_office_type_desc_chn=REPLACE(c_office_type_desc_chn,'"+i+"',''),c_parent_name='"+i+"' where c_parent_id like '16%' and  c_office_type_desc_chn like '"+i+"%'"
        print(sql)
        cur2.execute(sql)
        conn2.commit()

def getBookByNameAndDynasty():
    sql="select c_personid,c_name_chn,c_dynasty_chn,tts_sysno from 人物基本表_new where tts_sysno>=45940 and gjs_bookid is null and c_dynasty_chn not in ('宋','辽','唐','元','吴','金','明','隋','秦','新','清','周') "
    cur2.execute(sql)
    datas=cur2.fetchall()
    for data in datas:
        person_id=data[0]
        person_name=data[1]
        dynasty=data[2]
        sysno=data[3]
        #kbase_sql="SELECT AFTER5_ENTRY,BEFORE5_ENTRY,BOOK,BOOK_ID,CODE,ENTRY_11,ENTRY_13,ENTRY_2,ENTRY_CLASS,ENTRY_ID,PARENT_NODE,PUB_DATE,SYS_NVSM_K,SYS_VSM FROM CRFD2008,CRFD2009,CRFD2010,CRFD2011,CRFD2012,CRFD2013,CRFD2014,CRFD2015,CRFD2016,CRFD2017,CRFD2018,CRFD2019,CRFD2020 where ENTRY_11='"+person_name+"' and (ENTRY_2 % '"+dynasty+"朝' or ENTRY_2 % '"+dynasty+"代' or ENTRY_2 % '"+dynasty+"国')"
        kbase_sql="SELECT AFTER5_ENTRY,BEFORE5_ENTRY,BOOK,BOOK_ID,CODE,ENTRY_11,ENTRY_13,ENTRY_2,ENTRY_CLASS,ENTRY_ID,PARENT_NODE,PUB_DATE,SYS_NVSM_K,SYS_VSM FROM CRFD2008,CRFD2009,CRFD2010,CRFD2011,CRFD2012,CRFD2013,CRFD2014,CRFD2015,CRFD2016,CRFD2017,CRFD2018,CRFD2019,CRFD2020 where ENTRY_11='"+person_name+"' and ENTRY_2 % '"+dynasty+"'"
        print(kbase_sql)
        cur_kbase.execute(kbase_sql,bunicode=True)
        books_info=cur_kbase.fetchall()
        if books_info:
            onedata=books_info[0]#获取每个人的第一条数据
            #下面集合的下标需要根据实际调整
            gjs_bookid=onedata[3]
            gjs_book=onedata[2]
            gjs_name=onedata[5]
            gjs_txt=onedata[7].strip()
            gjs_time=onedata[11]
            gjs_keyword=onedata[12]
            gjs_vsm=onedata[13]
            gjs_entry_id=onedata[9]
            gjs_url="https://gongjushu.cnki.net/RBook/Detail?entryId="+gjs_entry_id
            gjs_before=onedata[1]
            gjs_after=onedata[0]
            gjs_code=onedata[4]
            gjs_parent_node=onedata[10]
            gjs_entry_class=onedata[8]
            gjs_person_relation=onedata[0]
            gjs_addr_relation=onedata[0]
            gjs_time_relation=onedata[0]
            gjs_org_relation=onedata[0]
            gjs_dy_relation=onedata[0]
            gjs_count=len(books_info)
            gjs_entries=onedata[0]
            bdyear=''
            #向人物工具书表_new添加工具书数据
            for book in books_info:
                flag=False
                #下面集合的下标需要根据实际调整，看ENTRY_13是第几个字段
                year=book[6]
                #print(year)
                if flag==False:
                    if year:
                        bdyear=year
                        flag=True
                datas=[]

                #下面集合的下标需要根据实际调整
                gjs_bookid=book[3]
                gjs_book=book[2]
                gjs_entry_id=book[9]
                data=(sysno,person_id,person_name,gjs_bookid,gjs_book,gjs_entry_id)
                datas.append(data)
                into_sql="insert into `人物工具书表_new`(tts_sysno,c_personid,c_name_chn,gjs_bookid,gjs_book,gjs_entry_id) values(%s,%s,%s,%s,%s,%s)"
                cur2.executemany(into_sql,datas)
                conn2.commit()
            #更新人物基本表_tmp中的缺失字段
            #print('bdyear='+bdyear)
            if "—" in bdyear:
                birth_year=bdyear.split('—')[0]#出生年份
                death_year=bdyear.split('—')[1]#去世年份
                update_sql="update `人物基本表_new` set gjs_bookid='"+gjs_bookid+"',gjs_book='"+gjs_book+"',gjs_name='"+gjs_name+"',gjs_txt='"+gjs_txt+"',gjs_time='"+gjs_time+"',gjs_keyword='"+gjs_keyword+"',gjs_vsm='"+gjs_vsm+"',gjs_entry_id='"+gjs_entry_id+"',gjs_url='"+gjs_url+"',gjs_before='"+gjs_before+"',gjs_after='"+gjs_after+"',gjs_code='"+gjs_code+"',gjs_parent_node='"+gjs_parent_node+"',gjs_entry_class='"+gjs_entry_class+"',gjs_count="+str(gjs_count)+",c_birthyear='"+birth_year+"',c_deathyear='"+death_year+"',c_by_to_death='"+bdyear+"' where c_name_chn='"+person_name+"' and c_personid="+str(person_id)+" and tts_sysno="+str(sysno)
            else:
                #update_sql="update `人物基本表_tmp` set gjs_bookid='"+gjs_bookid+"',gjs_book='"+gjs_book+"',gjs_name='"+gjs_name+"',gjs_txt='"+gjs_txt+"',gjs_time='"+gjs_time+"',gjs_keyword='"+gjs_keyword+"',gjs_vsm='"+gjs_vsm+"',gjs_entry_id='"+gjs_entry_id+"',gjs_url='"+gjs_url+"',gjs_before='"+gjs_before+"',gjs_after='"+gjs_after+"',gjs_code='"+gjs_code+"',gjs_parent_node='"+gjs_parent_node+"',gjs_entry_class='"+gjs_entry_class+"',gjs_person_relation='"+gjs_person_relation+"',gjs_addr_relation='"+gjs_addr_relation+"',gjs_time_relation='"+gjs_time_relation+"',gjs_org_relation='"+gjs_org_relation+"',gjs_dy_relation='"+gjs_dy_relation+"',gjs_count='"+str(gjs_count)+"',gjs_entries='"+gjs_entries+"',c_birthyear="+str(birth_year)+"',c_deathyear="+str(death_year)+"',c_by_to_death='"+bdyear+" where c_name_chn='"+name+"' and tts_sysno="+str(sysno)
                update_sql="update `人物基本表_new` set gjs_bookid='"+gjs_bookid+"',gjs_book='"+gjs_book+"',gjs_name='"+gjs_name+"',gjs_txt='"+gjs_txt+"',gjs_time='"+gjs_time+"',gjs_keyword='"+gjs_keyword+"',gjs_vsm='"+gjs_vsm+"',gjs_entry_id='"+gjs_entry_id+"',gjs_url='"+gjs_url+"',gjs_before='"+gjs_before+"',gjs_after='"+gjs_after+"',gjs_code='"+gjs_code+"',gjs_parent_node='"+gjs_parent_node+"',gjs_entry_class='"+gjs_entry_class+"',gjs_count="+str(gjs_count)+",c_by_to_death='"+bdyear+"' where c_name_chn='"+person_name+"' and c_personid="+str(person_id)+" and tts_sysno="+str(sysno)
            print(update_sql)
            try:
                cur2.execute(update_sql)
                conn2.commit()
            except:
                print(sysno)

def test():
    sel="select c_office_type_node_id from 官职表  where c_office_type_desc_chn='' and c_office_id is null"
    cur2.execute(sel)
    datas=cur2.fetchall()
    for data in datas:
        node_id=data[0]
        sql="select c_office_type_desc_chn from `官职_政府机构_简` where c_office_type_node_id="+str(node_id)
        cur2.execute(sql)
        name=cur2.fetchone()
        name=name[0].replace('\ufeff','')
        sql2="update 官职表 set c_office_type_desc_chn='"+name+"' where c_office_type_node_id="+str(node_id)
        print(sql2)
        cur2.execute(sql2)
        conn2.commit()

def addNodeId():
    sql="select * from `朝代官制表_copy` where c_office_type_node_id is null and c_dynasty_chn='春秋' "
    cur2.execute(sql)
    datas=cur2.fetchall()
    parentid=None
    id=1
    values=[]
    for data in datas:
        office_name=data[3]
        parent_id=data[4]
        if parentid==None:
            parentid=parent_id
        if parent_id!=parentid:
            id=1
            parentid=parent_id
        if id<10:
            node_id=parent_id+'0'+str(id)
            id=id+1
        else:
            node_id=parent_id+str(id)
            id=id+1
        print("node_id="+node_id+"  parent_id="+parent_id)
        update="update `朝代官制表_copy` set c_office_type_node_id=%s where c_office_type_desc_chn=%s and c_parent_id=%s and c_dynasty_chn='春秋'"
        value=(node_id,office_name,parent_id)
        values.append(value)
        cur2.executemany(update,values)
        conn2.commit()

def adjustparentid():
    sql="select c_office_type_node_id from `朝代官制表_copy` where c_dynasty_chn='商朝'"
    cur2.execute(sql)
    ids=cur2.fetchall()
    for id in ids:
        id=id[0]
        parentid=id[0:len(id)-2]
        update="update `朝代官制表_copy` set c_parent_id='"+parentid+"' where c_office_type_node_id='"+id+"'"
        print(update)
        cur2.execute(update)
        conn2.commit()

def adjustofficeid():
    sql="select c_office_id from  `官职表_new` group by c_office_id having count(0)>1"
    cur2.execute(sql)
    officeids=cur2.fetchall()

    for officeid in officeids:
        if officeid[0]!=None:
            sql1="select sysno,c_office_type_desc_chn,c_parent_name from `官职表_new` where c_office_id="+str(officeid[0])
            print(sql1)
            cur2.execute(sql1)
            datas=cur2.fetchall()
            num=1
            for data in datas:
                sysno=data[0]
                office_name=data[1]
                parent_name=data[2]
                new_name="("+parent_name+")"+office_name
                if num==1:
                    update="update `官职表_new` set c_office_type_desc_chn='"+new_name+"' where sysno="+str(sysno)
                    num=num+1
                else:
                    update="update `官职表_new` set c_office_type_desc_chn='"+new_name+"',c_office_id=null where sysno="+str(sysno)
                    num=num+1
                print(update)
                cur2.execute(update)
                conn2.commit()

def adjustofficesort():
    sql="select sysno,c_office_type_node_id from `官职表_new` where LENGTH(c_parent_id)=2 and c_office_sort is null order by c_dynasty_chn,c_office_sort"
    cur2.execute(sql)
    datas=cur2.fetchall()
    for data in datas:
        sysno=data[0]
        node_id=data[1]
        sort=node_id[2:]
        if sort.startswith('0'):
            sort=sort.replace('0','')
        update="update `官职表_new` set c_office_sort="+sort+" where sysno="+str(sysno)
        print(update)
        cur2.execute(update)
        conn2.commit()

def aa():
    sql="select sysno,c_office_type_node_id from `官职表_new` where c_dynasty_chn='西夏'"
    cur2.execute(sql)
    ids = cur2.fetchall()
    for id in ids:
        sysno=id[0]
        node_id=id[1]
        parentid=node_id[0:-2]
        update = "update `官职表_new` set c_parent_id='"+str(parentid) +"' where sysno="+str(sysno)
        cur2.execute(update)
        conn2.commit()

#事件表事件名称清理
def adjusteventtable():
    sql="select id,c_event_chn from `事件表` "
    #sql="select CategoryNodeID,CategoryNodeName from `categorynode` where label ='humantity_event'  and CategoryNodeName like '%实例%'"
    cur2.execute(sql)
    datas=cur2.fetchall()
    begin=r'^[0-9]{1,}\.' #匹配所有数字开头，后面一个'.'的
    begin3=r'^[0-9]{1,}' #匹配所有数字开头
    begin1=r'(\(.*?\))'
    begin2=r'(\（.*?\）)'
    begin4=r'^[0-9]{1,}年[0-9]{1,}月[0-9]{1,}日'
    begin5=r'^[0-9]{1,}—[0-9]{1,}年'
    begin6=r'^[0-9]{1,}-[0-9]{1,}-[0-9]{1,}-[0-9]{1,}'
    begin7=r'^[A-Za-z]{1,}[0-9]{1,}·[0-9]{1,}'
    begin8=r'^[0-9]{1,}\.[0-9]{1,}\.[0-9]{1,}'
    begin9=r'实例[0-9]{1,}'
    begin10=r'实例简介——'
    begin11=r'第\w节'
    begin12=r'^[A-Za-z]{1,}[0-9]{1,}'
    begin13=r'^[A-Z]{1,}[0-9]{1,}\?{1,}[0-9]{1,}'
    begin14=r'【教学案例】'
    begin15=r'第\w章'
    begin16=r'^附\w{1,}:'
    begin17=r'\w{1,}篇:'
    begin18=r'^附\w{0,}：'
    begin19=r'^[0-9]{0,}、'
    begin20=r'[A-Z]{1,}、'
    span=r'\<(.*)\>'
    shu=['一、','二、','三、','四、','五、','六、','七、','八、','九、','十、','十一、','十二、','十三、','十四、','十五、','十六、','十七、','十八、','十九、','二十、','二十一、','二十二、','三十五、']
    shu1=['一','二','三','四','五','六','七','八','九','十']
    zi=['一　','二  ','三　','四　','五　','六　','七　','八　','九　','十　','十一　','十二　','十三　','十四　','十五　','十六　','十七　','十八　','十九　','二十　']
    shuzi=['一 ','二 ','三 ','四 ','五 ','六 ','七 ','八 ','九 ','十 ','十一 ','十二 ','十三 ','十四 ','十五 ','十六 ','十七 ','十八 ','十九 ','二十 ']
    for data in datas:
        id=data[0]
        event=data[1]
        for s in shu:
            if event.startswith(s):
                event=event.replace(s,'').replace(' ','')
        for a in zi:
            if event.startswith(a):
                event=event.replace(a,'').replace(' ','')
        for a in shuzi:
            if event.startswith(a):
                event=event.replace(a,'').replace(' ','')
        de8=re.findall(begin8,event)
        if de8:
            event=event.replace(de8[0],'').replace(' ','')#去掉数字开头，后面两个.的内容
        de1=re.findall(begin,event)
        if de1:
            event=event.replace(de1[0],'').replace(' ','')#去掉数字开头，紧跟'.'的内容
        de6=re.findall(begin6,event)
        if de6:
            event=event.replace(de6[0],'').replace(' ','')#去掉x-x-x-x开头的内容

        de4=re.findall(begin4,event)
        if de4:
            event=event.replace(de4[0],'')#去掉年月日开头的内容
        de5=re.findall(begin5,event)
        if de5:
            event=event.replace(de5[0],'')#去掉xxx-xxx年开头的内容
        de3=re.findall(begin3,event)
        if de3:
            event=event.replace(de3[0],'').replace('  ',' ').replace(' ','')#去掉数字开头的内容
        de7=re.findall(begin7,event)
        if de7:
            event=event.replace(de7[0],'').replace(' ','')#去掉G04013·013年开头的内容
        de9=re.findall(begin9,event)
        if de9:
            event=event.replace(de9[0],'').replace(' ','')
        de10=re.findall(begin10,event)
        if de10:
            event=event.replace(de10[0],'').replace(' ','')
        de11=re.findall(begin11,event)
        if de11:
            event=event.replace(de11[0],'').replace(' ','')
        de12=re.findall(begin12,event)
        if de12:
            event=event.replace(de12[0],'').replace(' ','')
        de13=re.findall(begin13,event)
        if de13:
            event=event.replace(de13[0],'').replace(' ','')
        de14=re.findall(begin14,event)
        if de14:
            event=event.replace(de14[0],'').replace(' ','')
        de15=re.findall(begin15,event)
        if de15:
            event=event.replace(de15[0],'').replace(' ','')
        de16=re.findall(begin16,event)
        if de16:
            event=event.replace(de16[0],'').replace(' ','')
        de17=re.findall(begin17,event)
        if de17:
            event=event.replace(de17[0],'').replace(' ','')
        de18=re.findall(begin18,event)
        if de18:
            event=event.replace(de18[0],'').replace(' ','')
        de19=re.findall(begin19,event)
        if de19:
            event=event.replace(de19[0],'').replace(' ','')
        de20=re.findall(begin20,event)
        if de20:
            event=event.replace(de20[0],'').replace(' ','')
        if '(' in event:
            de=re.findall(begin1,event)
            event=event.replace(de[0],'').replace(' ','')
        if '（' in event:
            de=re.findall(begin2,event)
            event=event.replace(de[0],'').replace(' ','')
        if '<' in event:
            de=re.findall(span,event)
            event=event.replace(de[0],'').replace('<','').replace('>','')
        if ' ' in event:
            events=event.split(' ',1)
            if '单元' in events[0]:
                event=event.replace(events[0],'').replace(' ','')
            elif '选修' in events[0]:
                event=event.replace(events[0],'').replace(' ','')
            elif '第' in events[0]:
                event=event.replace(events[0],'').replace(' ','')
            elif '图' in events[0]:
                event=event.replace(events[0],'').replace(' ','')
            elif '案例' in events[0]:
                event=event.replace(events[0],'').replace(' ','')
            elif '表' in events[0]:
                event=event.replace(events[0],'').replace(' ','')
            for s in shu1:
                if s in events[0]:
                    event=event.replace(events[0],'').replace(' ','')
        if '　' in event:
            events=event.split('　',1)
            if '单元' in events[0]:
                event=event.replace(events[0],'').replace('　','')
        update="update 事件表 set c_event_chn='"+event+"' where id="+str(id)
        # update="update categorynode set CategoryNodeName=%s,formulaGongju=%s,formulaZhuzuo=%s,formulaLunwen=%s  where CategoryNodeID=%s"
        # value=(event,"keyword=\'"+event+"\'","简介=\'"+event+"\'","主题=\'"+event+"\'",id)
        cur2.execute(update)
        conn2.commit()

#事件表添加事件名称首字母
def geteventpinyin():
    sql="select id,c_event_chn,c_event_name from `事件表` "
    cur2.execute(sql)
    datas=cur2.fetchall()
    pattern=r'[\u4e00-\u9fa5]'
    for data in datas:
        id=data[0]
        event=data[1]
        chinese=re.findall(pattern,event)
        if chinese:
            zi=chinese[0]
            pinyin=lazy_pinyin(zi)[0][:1].capitalize()
            update="update 事件表 set c_event_name='"+pinyin+"' where id="+str(id)
            print(update)
            cur2.execute(update)
            conn2.commit()

#事件表去重
def eventdistinct():
    sql="select count(1) ,c_event_chn from 事件表 group by c_event_chn having count(1)>1 "
    cur2.execute(sql)
    datas=cur2.fetchall()
    for data in datas:
        event=data[1]
        sql1="select id,c_event_chn from 事件表 where c_event_chn='"+event+"'"
        cur2.execute(sql1)
        ids=cur2.fetchall()
        for i in range(len((ids))):
            if i>0:
                id=ids[i][0]
                event=ids[i][1]
                delete="delete from 事件表 where id="+str(id)
                print(delete)
                cur2.execute(delete)
                conn2.commit()

def getSpaceData():
    sql="select c_addr_id,c_name_chn from `地区基本表_简` where gjs_book is null "
    cur2.execute(sql)
    spaces=cur2.fetchall()
    for space in spaces:
        try:
            addr_id=space[0]
            space_name=space[1]
            #kbase_sql="SELECT AFTER5_ENTRY,BEFORE5_ENTRY,BOOK,BOOK_ID,CODE,ENTRY_11,ENTRY_13,ENTRY_2,ENTRY_CLASS,ENTRY_ID,PARENT_NODE,PUB_DATE,SYS_NVSM_K,SYS_VSM FROM CRFD2008,CRFD2009,CRFD2010,CRFD2011,CRFD2012,CRFD2013,CRFD2014,CRFD2015,CRFD2016,CRFD2017,CRFD2018,CRFD2019,CRFD2020 where ENTRY_11='"+space_name+"' and ENTRY_2 % '"+space_name+"'"
            kbase_sql="SELECT AFTER5_ENTRY,BEFORE5_ENTRY,BOOK,BOOK_ID,CODE,ENTRY_11,ENTRY_13,ENTRY_2,ENTRY_CLASS,ENTRY_ID,PARENT_NODE,PUB_DATE,SYS_NVSM_K,SYS_VSM FROM CRFD2008,CRFD2009,CRFD2010,CRFD2011,CRFD2012,CRFD2013,CRFD2014,CRFD2015,CRFD2016,CRFD2017,CRFD2018,CRFD2019,CRFD2020 where ENTRY_11='"+space_name+"'"
            print(kbase_sql)
            cur_kbase.execute(kbase_sql,bunicode=True)
            space_info=cur_kbase.fetchall()
            if space_info:
                onedata=space_info[0]#获取每个地点的第一条数据
                #下面集合的下标需要根据实际调整
                gjs_bookid=onedata[3]
                gjs_book=onedata[2]
                gjs_name=onedata[5]
                gjs_txt=onedata[7].strip()
                gjs_time=onedata[11]
                gjs_keyword=onedata[12]
                gjs_vsm=onedata[13]
                gjs_entry_id=onedata[9]
                gjs_url="https://gongjushu.cnki.net/RBook/Detail?entryId="+gjs_entry_id
                gjs_before=onedata[1]
                gjs_after=onedata[0]
                gjs_code=onedata[4]
                gjs_parent_node=onedata[10]
                gjs_entry_class=onedata[8]
                gjs_person_relation=onedata[0]
                gjs_addr_relation=onedata[0]
                gjs_time_relation=onedata[0]
                gjs_org_relation=onedata[0]
                gjs_dy_relation=onedata[0]
                gjs_count=len(space_info)
                gjs_entries=onedata[0]

                update = "update `地区基本表_简` set gjs_bookid='"+gjs_bookid+"',gjs_book='"+gjs_book+"',gjs_name='"+gjs_name+"',gjs_txt='"+gjs_txt+"',gjs_time='"+gjs_time+"',gjs_keyword='"+gjs_keyword+"',gjs_vsm='"+gjs_vsm+"',gjs_entry_id='"+gjs_entry_id+"',gjs_url='"+gjs_url+"',gjs_before='"+gjs_before+"',gjs_after='"+gjs_after+"',gjs_code='"+gjs_code+"',gjs_parent_node='"+gjs_parent_node+"',gjs_entry_class='"+gjs_entry_class+"',gjs_count="+str(gjs_count)+" where c_name_chn='"+space_name+"' and c_addr_id="+str(addr_id)
                cur2.execute(update)
                conn2.commit()
        except:
            continue

#向SPACETIMESEARCH导入事件表数据
def insertintokbaseevent():
    d={}
    sql1=" select c_dy,c_dynasty_chn from `朝代_简_copy`"
    cur2.execute(sql1)
    dys=cur2.fetchall()
    for dy in dys:
        dy_id=dy[0]
        dy_name=dy[1]
        d[dy_id]=dy_name
    print(d)
    sql="select id,c_event_chn,c_dynasty_chn,c_dy,c_dy_parentid,gjs_entry_id,isdelete,c_addr_chn from `humantity_event` where isdelete=0 "
    cur3.execute(sql)
    datas=cur3.fetchall()
    print(len(datas))
    values=[]
    for data in datas:
        id=data[0]
        event_name=data[1]
        dynasty=data[2]
        dy_id=data[3]
        parent_dy=data[4]
        gjs_entry_id=data[5]
        isdelete=data[6]
        addr_chn=data[7]
        x_coord=None
        y_coord=None
        if addr_chn:
            addr=addr_chn.split(';')[0]
            sql3="select x_coord,y_coord  from `humantity_place` where x_coord is not null and c_name_chn='"+addr+"'"
            cur3.execute(sql3)
            place=cur3.fetchall()
            if place:
                x_coord=place[0][0]
                y_coord=place[0][1]
        if x_coord:
            print('有坐标id：'+str(id))
            if parent_dy==0:
                insert = "insert into SPACETIMESEARCH(STSNAME,STSTYPE,C_DY,C_DY_NAME,STS_ID,GJS_ENTRY_ID,C_DY_PARENTID,NEOJ_LABEL,ISDELETE,X_COORD,Y_COORD) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                value=(event_name,'事件',str(dy_id),dynasty,str(id),gjs_entry_id,str(0),'humantity_event',str(isdelete),str(x_coord),str(y_coord))
                cur_kbase_89.execute(insert,value)
                conn_kbase_89.commit()

            elif parent_dy==None:
                insert = "insert into SPACETIMESEARCH(STSNAME,STSTYPE,C_DY,C_DY_NAME,STS_ID,GJS_ENTRY_ID,NEOJ_LABEL,ISDELETE,X_COORD,Y_COORD) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                value=(event_name,'事件',str(dy_id),dynasty,str(id),gjs_entry_id,'humantity_event',str(isdelete),str(x_coord),str(y_coord))
                cur_kbase_89.execute(insert,value)
                conn_kbase_89.commit()
            else:
                parent_dy_chn=d[int(parent_dy)]
                if parent_dy_chn:
                    insert = "insert into SPACETIMESEARCH(STSNAME,STSTYPE,C_DY,C_DY_NAME,STS_ID,GJS_ENTRY_ID,C_DY_PARENTID,C_DY_PNAME,NEOJ_LABEL,ISDELETE,X_COORD,Y_COORD) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                    value=(event_name,'事件',str(dy_id),dynasty,str(id),gjs_entry_id,str(parent_dy),parent_dy_chn,'humantity_event',str(isdelete),str(x_coord),str(y_coord))
                    cur_kbase_89.execute(insert,value)
                    conn_kbase_89.commit()
        else:
            print('无坐标id：'+str(id))
            if parent_dy==0:
                insert = "insert into SPACETIMESEARCH(STSNAME,STSTYPE,C_DY,C_DY_NAME,STS_ID,GJS_ENTRY_ID,C_DY_PARENTID,NEOJ_LABEL,ISDELETE) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                value=(event_name,'事件',str(dy_id),dynasty,str(id),gjs_entry_id,str(0),'humantity_event',str(isdelete))
                cur_kbase_89.execute(insert,value)
                conn_kbase_89.commit()

            elif parent_dy==None:
                insert = "insert into SPACETIMESEARCH(STSNAME,STSTYPE,C_DY,C_DY_NAME,STS_ID,GJS_ENTRY_ID,NEOJ_LABEL,ISDELETE) values(%s,%s,%s,%s,%s,%s,%s,%s)"
                value=(event_name,'事件',str(dy_id),dynasty,str(id),gjs_entry_id,'humantity_event',str(isdelete))
                cur_kbase_89.execute(insert,value)
                conn_kbase_89.commit()
            else:
                parent_dy_chn=d[int(parent_dy)]
                if parent_dy_chn:
                    insert = "insert into SPACETIMESEARCH(STSNAME,STSTYPE,C_DY,C_DY_NAME,STS_ID,GJS_ENTRY_ID,C_DY_PARENTID,C_DY_PNAME,NEOJ_LABEL,ISDELETE) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                    value=(event_name,'事件',str(dy_id),dynasty,str(id),gjs_entry_id,str(parent_dy),parent_dy_chn,'humantity_event',str(isdelete))
                    cur_kbase_89.execute(insert,value)
                    conn_kbase_89.commit()

#向SPACETIMESEARCH导入地区表数据
def insertintokbasespace():
    d={}
    sql1=" select c_dy,c_dynasty_chn from `朝代_简_copy`"
    cur2.execute(sql1)
    dys=cur2.fetchall()
    for dy in dys:
        dy_id=dy[0]
        dy_name=dy[1]
        d[dy_id]=dy_name
    print(d)
    sql="select c_addr_id,c_name_chn,gjs_entry_id,isdelete,x_coord,y_coord from `地区基本表_简` "
    cur2.execute(sql)
    datas=cur2.fetchall()
    values=[]
    for data in datas:
        id=data[0]
        space_name=data[1]
        gjs_entry_id=data[2]
        isdelete=data[3]
        x_coord=data[4]
        y_coord=data[5]
        if x_coord:
            if gjs_entry_id:
                insert = "insert into SPACETIMESEARCH(STSNAME,STSTYPE,STS_ID,GJS_ENTRY_ID,NEOJ_LABEL,ISDELETE,X_COORD,Y_COORD) values(%s,%s,%s,%s,%s,%s,%s,%s)"
                value=(space_name,'地区',str(id),gjs_entry_id,'humantity_place',str(isdelete),str(x_coord),str(y_coord))
            else:
                insert = "insert into SPACETIMESEARCH(STSNAME,STSTYPE,STS_ID,NEOJ_LABEL,ISDELETE,X_COORD,Y_COORD) values(%s,%s,%s,%s,%s,%s,%s)"
                value=(space_name,'地区',str(id),'humantity_place',str(isdelete),str(x_coord),str(y_coord))
        else:
            if gjs_entry_id:
                insert = "insert into SPACETIMESEARCH(STSNAME,STSTYPE,STS_ID,GJS_ENTRY_ID,NEOJ_LABEL,ISDELETE) values(%s,%s,%s,%s,%s,%s)"
                value=(space_name,'地区',str(id),gjs_entry_id,'humantity_place',str(isdelete))
            else:
                insert = "insert into SPACETIMESEARCH(STSNAME,STSTYPE,STS_ID,NEOJ_LABEL,ISDELETE) values(%s,%s,%s,%s,%s)"
                value=(space_name,'地区',str(id),'humantity_place',str(isdelete))
        cur_kbase_89.execute(insert,value)
        conn_kbase_89.commit()

#将地点去重表中的数据导入SPACETIMESEARCH
def insertintospace():
    d={}
    sql1=" select c_dy,c_dynasty_chn from `朝代_简_copy`"
    cur2.execute(sql1)
    dys=cur2.fetchall()
    for dy in dys:
        dy_id=dy[0]
        dy_name=dy[1]
        d[dy_name]=dy_id
    print(d)
    sql="select * from 地点去重"
    cur2.execute(sql)
    datas=cur2.fetchall()
    for data in datas:
        print(data)
        place=data[0]
        dynasty=data[1]
        parent_dy=data[2]
        entryid=data[3]
        x_coord=data[4]
        y_coord=data[5]
        dy_id=d[dynasty]
        parent_id=d[parent_dy]
        insert="insert into SPACETIMESEARCH(STSNAME,STSTYPE,C_DY,C_DY_NAME,GJS_ENTRY_ID,C_DY_PARENTID,C_DY_PNAME,NEOJ_LABEL,ISDELETE,X_COORD,Y_COORD) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) "
        value=(place,'地点',str(dy_id),dynasty,entryid,str(parent_id),parent_dy,'humantity_place',str(0),str(x_coord),str(y_coord))
        cur_kbase_89.execute(insert,value)
        conn_kbase_89.commit()

#将官职和人物表信息转为REC文件
def insertkbasegov():
    sql="select tts_sysno,gjs_bookid,gjs_book,gjs_name,gjs_txt,gjs_time,gjs_parent_node,gjs_vsm,gjs_keyword,gjs_entry_id,gjs_url,gjs_before,gjs_after,gjs_code,gjs_entry_class from humantity_persons where gjs_book is not null "
    cur3.execute(sql)
    span=r'(\<.*?\>)'
    span1=r'(\&.*?\;)'
    datas = cur3.fetchall()
    w=open("C:\\Users\\diao\\Desktop\\person.txt","a",encoding='gb18030')
    for data in datas:
        id=data[0]
        bookid=data[1]
        book=data[2]
        name=data[3]
        txt=data[4]
        de=re.findall(span,txt)
        if de:
            for i in range(len(de)):
                txt=txt.replace(de[i],'')
        txt=txt.replace('<','')
        time=data[5]
        parent_node=data[6]
        vsm = data[7]
        keyword=data[8]
        entry_id=data[9]
        url="https://gongjushu.cnki.net/RBook/Detail?entryId="+entry_id
        before=data[11]
        de1=re.findall(span1,before)
        if de1:
            before=before.replace(de1[0],'')
        after=data[12]
        code=data[13]
        entry_class=data[14]
        if time:
            year=time.split('-')[0]
        sql1="SELECT PARENT_CODE FROM CRFD2008,CRFD2009,CRFD2010,CRFD2011,CRFD2012,CRFD2013,CRFD2014,CRFD2015,CRFD2016,CRFD2017,CRFD2018,CRFD2019,CRFD2020 where CODE="+code+" limit 1"
        cur_kbase.execute(sql1)
        parent_code=cur_kbase.fetchone()[0]
        # print(name)
        all=[]
        all.append('<REC>')
        rec_id='<ID>='+str(id);all.append(rec_id)
        rec_bookid='<BOOKID>='+bookid;all.append(rec_bookid)
        rec_book='<BOOK>='+book;all.append(rec_book)
        rec_name='<NAME>='+name;all.append(rec_name)
        rec_txt='<TXT>='+txt;all.append(rec_txt)
        if time:
            rec_time='<TIME>='+time;all.append(rec_time)
        rec_parent_node='<PARENT_NODE>='+parent_node;all.append(rec_parent_node)
        rec_vsm='<VSM>='+vsm;all.append(rec_vsm)
        rec_keyword='<KEYWORD>='+keyword;all.append(rec_keyword)
        rec_entry_id='<ENTRY_ID>='+entry_id;all.append(rec_entry_id)
        rec_url="<URL>="+url;all.append(rec_url)
        rec_before='<BEFORE>='+before;all.append(rec_before)
        rec_after='<AFTER>='+after;all.append(rec_after)
        rec_code='<CODE>='+code;all.append(rec_code)
        rec_parent_code='<PARENT_CODE>='+parent_code;all.append(rec_parent_code)
        rec_entry_class='<ENTITY_CLASS>='+entry_class;all.append(rec_entry_class)
        rec_type='<TYPE>='+'事件';all.append(rec_type)
        if year:
            rec_year='<YEAR>='+year;all.append(rec_year)
        all='\n'.join(all)
        w.write(all+'\n')
        # if time:
        #     insert="insert into WX_PERSON_TMP(ID,BOOKID,BOOK,`NAME`,TXT,`TIME`,PARENT_NODE,VSM,KEYWORD,ENTRY_ID,URL,BEFORE,AFTER,CODE,PARENT_CODE,ENTITY_CLASS,`TYPE`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        #     value=(str(id),bookid,book,name,txt,time,parent_node,vsm,keyword,entry_id,url,before,after,code,parent_code,entry_class,'人物')
        # else:
        #     insert="insert into WX_PERSON_TMP(ID,BOOKID,BOOK,`NAME`,TXT,PARENT_NODE,VSM,KEYWORD,ENTRY_ID,URL,BEFORE,AFTER,CODE,PARENT_CODE,ENTITY_CLASS,`TYPE`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        #     value=(str(id),bookid,book,name,txt,parent_node,vsm,keyword,entry_id,url,before,after,code,parent_code,entry_class,'人物')
        # print(value)


        # cur_kbase_89.execute(insert,value)
        # conn_kbase_89.commit()
    w.close()

#向categorynode表添加人物数据
def createRw():
    sql="SELECT c_personid,gjs_entry_id,c_name_chn,c_name,c_birthyear,c_deathyear,c_dynasty_chn,c_dy,c_dy_type,c_parentid,c_status_desc_chn,c_status_code,c_status_type_name,c_status_type_code from `人物基本表_new` where c_dynasty_chn is not  null and isdelete=0 "
    cur2.execute(sql)
    datas = cur2.fetchall()
    dy_type_list=[]
    maplist=[]#所有数据字典的集合
    pat=r'[0-9]{1,}'
    for data in datas:
        map={}#每条数据的字段和数值对应的字典
        personid=data[0]
        entry_id=data[1];
        name_chn=data[2];map['c_name_chn']=name_chn #名字中文
        name=data[3];map['c_name']=name #名字全拼
        birthyear=data[4];map['c_birthyear']=birthyear
        deathyear=data[5];map['c_deathyear']=deathyear
        dynasty=data[6]
        dy=data[7];map['c_dy']=dy
        dy_type=data[8]
        parentid=data[9];map['c_parentid']=parentid
        status_desc_chn=data[10]#人物类别
        status_code=data[11];map['c_status_code']=status_code#类别编号
        status_type_name=data[12]#部名称
        status_type_code=data[13];map['c_status_type_code']=status_type_code#部编号
        if dy_type:
            if dy_type not in dy_type_list:
                dy_type_list.append(dy_type)
        if dy_type==None:#一级朝代
            dy_type='未详'
        map['c_dy_type']=dy_type
        if dynasty==None:#二级朝代
            dynasty='未详'
        map['c_dynasty_chn']=dynasty
        if status_type_name==None:#部
            status_type_name='未详'
        map['c_status_type_name']=status_type_name
        if status_desc_chn==None:#类别
            status_desc_chn='未详'
        map['c_status_desc_chn']=status_desc_chn
        if '部' in status_desc_chn:
            status_desc_chn='未详'
        map['c_status_desc_chn']=status_desc_chn
        if entry_id==None:
            entry_id=''
        map['gjs_entry_id']=entry_id
        if personid==None:
            personid=''
        map['c_personid']=personid
        maplist.append(map)
    total = len(datas)
    i=0
    value=()
    id=6
    #遍历一级朝代
    pid=id

    for dy_type in dy_type_list:
        CategoryNodeName=dy_type
        Parentid=1
        Birth=0
        CategoryTypeID=1
        Death=0
        PinYin=''
        level=1
        IsLeaf=1
        Formula=''
        cdFirstId=0
        value=(dy_type,1,0,1,0,'',1,1)
        cdChildList=[]
        #遍历数据集
        for map in maplist:
            if map['c_dy_type']:
                if map['c_dy_type']==dy_type:#如果数据有父朝代且和当前父朝代一致
                    c_dynasty=map['c_dynasty_chn']#获取子朝代
                    if c_dynasty not in cdChildList:
                        cdChildList.append(c_dynasty)#将同一个父朝代的所有子朝代放到集合中
        #没有子朝代且父朝代为'未详',退出循环
        if len(cdChildList)==0:
            if dy_type=='未详':
                continue
        #如果有子朝代
        if len(cdChildList)>0:
            value=list(value)
            value[7]=0
            i=i+1
            if id<10:
                node_code='0001'+'000'+str(id)
            elif id<100:
                node_code='0001'+'00'+str(id)
            elif id<1000:
                node_code='0001'+'0'+str(id)
            else:
                node_code='0001'+str(id)
            value.append(node_code)
            insert = "insert into `categorynode_tmp`(CategoryNodeName,parentid,birth,CategoryTypeID,death,pinyin,level,isLeaf,CategoryNodeCode) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            value=tuple(value)
            cur2.execute(insert,value)
            conn2.commit()
            id=id+1
            cdFirstId=''
        #遍历子朝代
        for cdChild in cdChildList:
            value1=(cdChild,pid,0,1,0,'',2,1)
            dwList=[]
            #遍历数据集
            for map in maplist:
                if map['c_dy_type']:
                    if map['c_dy_type']==dy_type:#如果数据有父朝代且和当前父朝代一致
                        if map['c_dynasty_chn']:
                            if map['c_dynasty_chn']==cdChild:
                                #获取部门
                                dw=map['c_status_type_name']
                                if dw not in dwList:#部门去重
                                    dwList.append(dw)
            if len(dwList)==0:
                if cdChild=='未详':
                    continue
            if len(dwList)>0:
                value1=list(value1)
                value1[7]=0
                if id<10:
                    node_code1=node_code+'000'+str(id)
                elif id<100:
                    node_code1=node_code+'00'+str(id)
                elif id<1000:
                    node_code1=node_code+'0'+str(id)
                else:
                    node_code1=node_code+str(id)
                value1.append(node_code1)
                insert = "insert into `categorynode_tmp`(CategoryNodeName,parentid,birth,CategoryTypeID,death,pinyin,level,isLeaf,CategoryNodeCode) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                value1=tuple(value1)
                cur2.execute(insert,value1)
                conn2.commit()
                pid=id
                id=id+1
                #遍历部门
                for dw in dwList:
                    value2=(dw,pid,0,1,0,'',3,1)
                    dwChildList=[]
                    #遍历数据集
                    for map in maplist:
                        if map['c_dy_type']:
                            if map['c_dy_type']==dy_type:#如果数据有父朝代且和当前父朝代一致
                                if map['c_dynasty_chn']:
                                    if map['c_dynasty_chn']==cdChild:
                                        #获取部门
                                        if map['c_status_type_name']:
                                            if map['c_status_type_name']==dw:
                                                dwChild=map['c_status_desc_chn']
                                                if dwChild not in dwChildList:
                                                    dwChildList.append(dwChild)
                    if len(dwChildList)==0:
                        if dw=='未详':
                            continue
                    if len(dwChildList)>0:
                        value2=list(value2)
                        value2[7]=0
                        if id<10:
                            node_code2=node_code1+'000'+str(id)
                        elif id<100:
                            node_code2=node_code1+'00'+str(id)
                        elif id<1000:
                            node_code2=node_code1+'0'+str(id)
                        else:
                            node_code2=node_code1+str(id)
                        value2.append(node_code2)
                        insert = "insert into `categorynode_tmp`(CategoryNodeName,parentid,birth,CategoryTypeID,death,pinyin,level,isLeaf,CategoryNodeCode) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                        value2=tuple(value2)
                        cur2.execute(insert,value2)
                        conn2.commit()
                        pid=id
                        id=id+1

                        for dwChild in dwChildList:
                            value3=(dwChild,pid,0,1,0,'',4,0)
                            rwList=[]
                            #遍历数据集
                            for map in maplist:
                                if map['c_dy_type']:
                                    if map['c_dy_type']==dy_type:#如果数据有父朝代且和当前父朝代一致
                                        if map['c_dynasty_chn']:
                                            if map['c_dynasty_chn']==cdChild:
                                                #获取部门
                                                if map['c_status_type_name']:
                                                    if map['c_status_type_name']==dw:
                                                        if map['c_status_desc_chn']:
                                                            if map['c_status_desc_chn']==dwChild:
                                                                #获取人物数据
                                                                rw={}
                                                                rw['name']=map['c_name_chn']
                                                                rw['pinyin']=map['c_name']
                                                                if map['c_birthyear']:
                                                                    if '?' in map['c_birthyear']:
                                                                        rw['birth']=0
                                                                    else:
                                                                        a=re.findall(pat,map['c_birthyear'])
                                                                        print(a)
                                                                        if a:
                                                                            rw['birth']=int(a[0])
                                                                        else:
                                                                            rw['birth']=0
                                                                else:
                                                                    rw['birth']=0
                                                                if map['c_deathyear']:
                                                                    if '?' in map['c_deathyear']:
                                                                        rw['death']=0
                                                                    else:
                                                                        a=re.findall(pat,map['c_deathyear'])
                                                                        if a:
                                                                            rw['death']=int(a[0])
                                                                        else:
                                                                            rw['death']=0
                                                                else:
                                                                    rw['death']=0
                                                                if map['c_personid']:
                                                                    rw['uuid']=map['c_personid']
                                                                else:
                                                                    rw['uuid']=None
                                                                if map['gjs_entry_id']:
                                                                    rw['entityid']=map['gjs_entry_id']
                                                                else:
                                                                    rw['entityid']=None
                                                                rw['lable']='humantity_persons'
                                                                rwList.append(rw)
                            if len(rwList)>0:
                                value3=list(value3)
                                value3[7]=0
                                if id<10:
                                    node_code3=node_code2+'000'+str(id)
                                elif id<100:
                                    node_code3=node_code2+'00'+str(id)
                                elif id<1000:
                                    node_code3=node_code2+'0'+str(id)
                                else:
                                    node_code3=node_code2+str(id)
                                value3.append(node_code3)
                                insert = "insert into `categorynode_tmp`(CategoryNodeName,parentid,birth,CategoryTypeID,death,pinyin,level,isLeaf,CategoryNodeCode) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                                value3=tuple(value3)
                                cur2.execute(insert,value3)
                                conn2.commit()
                                pid=id
                                id=id+1

                                for rw in rwList:
                                    value4=(rw['name'],pid,rw['birth'],1,rw['death'],rw['pinyin'],5,1)
                                    value4=list(value4)
                                    if id<10:
                                        node_code4=node_code3+'000'+str(id)
                                    elif id<100:
                                        node_code4=node_code3+'00'+str(id)
                                    elif id<1000:
                                        node_code4=node_code3+'0'+str(id)
                                    else:
                                        node_code4=node_code3+str(id)
                                    value4.append(node_code4)
                                    value4.append(rw['uuid'])
                                    value4.append(rw['entityid'])
                                    value4.append(rw['lable'])
                                    insert4 = "insert into `categorynode_tmp`(CategoryNodeName,parentid,birth,CategoryTypeID,death,pinyin,level,isLeaf,CategoryNodeCode,uuid,entityid,label) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                                    value4=tuple(value4)
                                    cur2.execute(insert4,value4)
                                    conn2.commit()
                                    id=id+1

#向categorynode表添加事件数据
def creatSj():
    local_time=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
    sql="SELECT id,gjs_entry_id,c_event_chn,c_event_name,c_dynasty_chn,c_dy,c_dy_type,c_dy_parentid,c_event_type,c_event_time from `事件表` where c_dynasty_chn is not  null and isdelete=0 "
    cur2.execute(sql)
    datas = cur2.fetchall()
    dy_type_list=[]
    maplist=[]#所有数据字典的集合
    pat=r'[0-9]{1,}'
    for data in datas:
        map={}
        event_id=data[0];map['event_id']=event_id
        entry_id=data[1];map['gjs_entry_id']=entry_id
        event_name=data[2];map['event_name']=event_name
        pinyin=data[3];map['pinyin']=pinyin
        dynasty=data[4];map['c_dynasty_chn']=dynasty
        dy=data[5];map['c_dy']=dy
        dy_type=data[6];map['c_dy_type']=dy_type
        parentid=data[7];map['c_parentid']=parentid
        event_type=data[8];map['c_event_type']=event_type
        event_time=data[9];map['event_time']=event_time
        if dy_type:
            if dy_type not in dy_type_list:
                dy_type_list.append(dy_type)
        maplist.append(map)
    select=""
    # cur2.execute(select)
    # id_tmp=cur2.fetchall()
    #id=int(id_tmp[0][0])
    id=209521
    max_id=209521
    #max_id=int(id_tmp[0][0])
    print("max_id="+str(max_id))
    pid=id
    #遍历一级朝代
    for dy_type in dy_type_list:
        print("一级朝代："+dy_type)
        sql4="select c_sort from `朝代_简_copy` where c_dynasty_chn='"+dy_type+"'"
        cur2.execute(sql4)
        cd=cur2.fetchall()
        if cd:
            dy1=cd[0][0]
        else:
            dy1=111
        value=(dy_type,5,1,'',1,1,dy1,local_time,local_time,1)
        cdChildList=[]
        #遍历数据集
        for map in maplist:
            if map['c_dy_type']:
                if map['c_dy_type']==dy_type:#如果数据有父朝代且和当前父朝代一致
                    c_dynasty=map['c_dynasty_chn']#获取子朝代
                    if c_dynasty not in cdChildList:
                        cdChildList.append(c_dynasty)#将同一个父朝代的所有子朝代放到集合中
        #没有子朝代且父朝代为'未详',退出循环
        if len(cdChildList)==0:
            if dy_type=='未详':
                continue
        #如果有子朝代
        if len(cdChildList)>0:
            value=list(value)
            value[5]=0
            if id<10:
                node_code='0005'+'000'+str(id)
            elif id<100:
                node_code='0005'+'00'+str(id)
            elif id<1000:
                node_code='0005'+'0'+str(id)
            else:
                node_code='0005'+str(id)
            value.append(node_code)
            insert = "insert into `categorynode_tmp`(CategoryNodeName,parentid,CategoryTypeID,pinyin,level,isLeaf,SortCode,CreateTime,ModifyTime,Status,CategoryNodeCode) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            value=tuple(value)
            cur2.execute(insert,value)
            conn2.commit()
            select="select CategoryNodeID from `categorynode_tmp` where CategoryNodeName='"+dy_type+"' and CategoryNodeID>="+str(max_id)
            cur2.execute(select)
            parent_id=cur2.fetchall()[0][0]
            id=id+1
        #遍历子朝代
        for cdChild in cdChildList:
            print("二级朝代："+cdChild)
            sql4="select c_sort from `朝代_简_copy` where c_dynasty_chn='"+cdChild+"'"
            # print(sql4)
            cur2.execute(sql4)
            cd=cur2.fetchall()
            if cd:
                dy1=cd[0][0]
            else:
                dy1=111
            value1=(cdChild,parent_id,1,'',2,1,dy1,local_time,local_time,1)
            typeList=[]
            #遍历数据集
            for map in maplist:
                if map['c_dy_type']:
                    if map['c_dy_type']==dy_type:#如果数据有父朝代且和当前父朝代一致
                        if map['c_dynasty_chn']:
                            if map['c_dynasty_chn']==cdChild:
                                #获取事件类型
                                dw=map['c_event_type']
                                if dw not in typeList:#事件类型去重
                                    typeList.append(dw)
            if len(typeList)==0:
                if cdChild=='未详':
                    continue
            if len(typeList)>0:
                value1=list(value1)
                value1[5]=0
                if id<10:
                    node_code1=node_code+'000'+str(id)
                elif id<100:
                    node_code1=node_code+'00'+str(id)
                elif id<1000:
                    node_code1=node_code+'0'+str(id)
                else:
                    node_code1=node_code+str(id)
                value1.append(node_code1)
                insert = "insert into `categorynode_tmp`(CategoryNodeName,parentid,CategoryTypeID,pinyin,level,isLeaf,SortCode,CreateTime,ModifyTime,Status,CategoryNodeCode) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                value1=tuple(value1)
                cur2.execute(insert,value1)
                conn2.commit()
                select1="select max(CategoryNodeID) from `categorynode_tmp` where CategoryNodeName='"+cdChild+"' and CategoryNodeID>="+str(max_id)
                cur2.execute(select1)
                parent_id1=cur2.fetchall()[0][0]
                pid=id
                id=id+1
                #遍历事件类型
                for dw in typeList:
                    print("事件类型："+dw)
                    value2=(dw,parent_id1,1,'',3,1,local_time,local_time,1)
                    sjList=[]
                    #遍历数据集
                    for map in maplist:
                        if map['c_dy_type']:
                            if map['c_dy_type']==dy_type:#如果数据有父朝代且和当前父朝代一致
                                if map['c_dynasty_chn']:
                                    if map['c_dynasty_chn']==cdChild:
                                        #获取事件类型
                                        if map['c_event_type']:
                                            if map['c_event_type']==dw:
                                                sj={}
                                                sj['name']=map['event_name']
                                                sj['pinyin']=map['pinyin']
                                                sj['entityid']=map['gjs_entry_id']
                                                sj['label']='humantity_event'
                                                sj['uuid']=map['event_id']
                                                sj['eventtime']=map['event_time']
                                                sjList.append(sj)

                    if len(sjList)==0:
                        if dw=='未详':
                            continue
                    if len(sjList)>0:
                        value2=list(value2)
                        value2[5]=0
                        if id<10:
                            node_code2=node_code1+'000'+str(id)
                        elif id<100:
                            node_code2=node_code1+'00'+str(id)
                        elif id<1000:
                            node_code2=node_code1+'0'+str(id)
                        else:
                            node_code2=node_code1+str(id)
                        value2.append(node_code2)
                        insert = "insert into `categorynode_tmp`(CategoryNodeName,parentid,CategoryTypeID,pinyin,level,isLeaf,CreateTime,ModifyTime,Status,CategoryNodeCode) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                        value2=tuple(value2)
                        cur2.execute(insert,value2)
                        conn2.commit()
                        select2="select CategoryNodeID from `categorynode_tmp` where CategoryNodeName='"+dw+"' and parentid="+str(parent_id1)+" and CategoryNodeID>="+str(max_id)
                        cur2.execute(select2)
                        parent_id2=cur2.fetchall()[0][0]
                        print("事件上级ID:"+str(parent_id2)+" ID:"+str(id))
                        pid=id
                        id=id+1
                        for sj in sjList:
                            print("事件："+sj['name'])
                            value3=(sj['name'],parent_id2,1,sj['pinyin'],4,1,local_time,local_time,1)
                            value3=list(value3)
                            if id<10:
                                node_code3=node_code2+'000'+str(id)
                            elif id<100:
                                node_code3=node_code2+'00'+str(id)
                            elif id<1000:
                                node_code3=node_code2+'0'+str(id)
                            else:
                                node_code3=node_code2+str(id)
                            value3.append(node_code3)
                            value3.append(sj['uuid'])
                            value3.append(sj['entityid'])
                            value3.append(sj['label'])
                            value3.append('keyword=\''+sj['name']+'\'')
                            value3.append('简介=\''+sj['name']+'\'')
                            value3.append('主题=\''+sj['name']+'\'')
                            value3.append(sj['eventtime'])
                            insert3 = "insert into `categorynode_tmp` (CategoryNodeName,parentid,CategoryTypeID,pinyin,level,isLeaf,CreateTime,ModifyTime,Status,CategoryNodeCode,uuid,entityid,label,formulaGongju,formulaZhuzuo,formulaLunwen,eventtime) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                            value3=tuple(value3)
                            cur2.execute(insert3,value3)
                            conn2.commit()
                            id=id+1

#向SPACETIMESEARCH添加地区坐标信息
def aujustkbasespace():
    sql= "select c_addr_id,x_coord,y_coord from `地区基本表_简` where x_coord is not null"
    cur2.execute(sql)
    datas = cur2.fetchall()
    for data in datas:
        id=data[0]
        x_coord=data[1]
        y_coord=data[2]
        update="update SPACETIMESEARCH set X_COORD='"+str(x_coord)+"',Y_COORD='"+str(y_coord)+"' where NEOJ_LABEL='humantity_place' and STS_ID='"+str(id)+"'"
        print(update)
        cur_kbase_89.execute(update)
        conn_kbase_89.commit()

#清除人物txt信息中的<>
def removePersonTxt():
    sql="select sysno,gjs_txt from 官职表_new where  gjs_txt like '%<%'"
    span=r'(\<.*?\>)'
    cur2.execute(sql)
    datas=cur2.fetchall()
    for data in datas:
        try:
            sysno = data[0]
            txt=data[1]
            de=re.findall(span,txt)
            if de:
                for i in range(len(de)):
                    txt=txt.replace(de[i],'')
            txt=txt.replace('<','')
            update="update 官职表_new set gjs_txt=%s where sysno=%s"
            value=(txt,sysno)
            cur2.execute(update,value)
            conn2.commit()
        except:
            print(sysno)
            continue

#调整categorynode表中 部 的排序
def adjustbuSort():
    bu=['帝王部',
        '后妃部',
        '武将部',
        '文臣部',
        '科技部',
        '宗教部',
        '书法部',
        '文学部',
        '教育部',
        '美术部',
        '隐逸部',
        '部外部',
        '戏曲部',
        '宗室部',
        '术数部',
        '经济部',
        '史学部',
        '哲学部',
        '体育部',
        '人民起义部',
        '外戚宦官部',
        '音乐部'
        '巾帼英雄部',
        '语言文字部',
        '未详']
    id=1
    for b in bu:
        sql="update categorynode set SortCode="+str(id)+" where CategoryNodeName='"+b+"' and level=3 and label='null'"
        print(sql)
        cur_szrw.execute(sql)
        conn_szrw.commit()
        id=id+1

#调整categorynode表中事件类型的排序
def adjustEventTypeSort():
    event_types=['政治',
     '军事',
     '法律',
     '经济',
     '社会',
     '民族',
     '科技',
     '中外关系',
     '文物考古',
     '宗教与信仰',
     '思想文化与教育']
    id=1
    for type in event_types:
        sql="update categorynode set SortCode="+str(id)+" where CategoryNodeName='"+type+"' and level=3 "
        print(sql)
        cur_szrw.execute(sql)
        conn_szrw.commit()
        id=id+1

#调整事件表中事件时间
def adjustEventTime():
    sql="select id,c_event_time from 事件表 where c_event_time like '%（%' "
    cur2.execute(sql)
    datas=cur2.fetchall()
    for data in datas:
        id=data[0]
        event_time=data[1]
        if '１' in event_time:
            event_time=event_time.replace('１','1')
        if '０' in event_time:
            event_time=event_time.replace('０','0')
        if '２' in event_time:
            event_time=event_time.replace('２','2')
        if '３' in event_time:
            event_time=event_time.replace('３','3')
        if '４' in event_time:
            event_time=event_time.replace('４','4')
        if '９' in event_time:
            event_time=event_time.replace('９','9')
        if '５' in event_time:
            event_time=event_time.replace('５','5')
        if '６' in event_time:
            event_time=event_time.replace('６','6')
        if '７' in event_time:
            event_time=event_time.replace('７','7')
        if '８' in event_time:
            event_time=event_time.replace('８','8')
        if '(' in event_time:
            event_time=event_time.replace('(','').replace(')','')
        if '（' in event_time:
            event_time=event_time.replace('（','').replace('）','')
        if '年' not in event_time:
            if '—' in event_time:
                event_times=event_time.split('—')
                event_time=event_times[0]+'年—'+event_times[1]+'年'
            elif '～' in event_time:
                event_times=event_time.split('～')
                event_time=event_times[0]+'年—'+event_times[1]+'年'
        update="update 事件表 set c_event_time='"+event_time+"' where id="+str(id)
        print(update)
        cur2.execute(update)
        conn2.commit()

#categorynode表添加事件时间，并修改事件名称
def addeventtime():
    sql="select id,c_event_chn,c_event_time from 事件表 "
    cur2.execute(sql)
    datas=cur2.fetchall()
    values=[]
    for data in datas:
        id=data[0]
        event_name=data[1]
        event_time=data[2]
        update="update categorynode set eventtime=%s,CategoryNodeName=%s where label='humantity_event' and uuid=%s"
        value=(event_time,event_name,id)
        print(value)
        values.append(value)
        if len(values)>100:
            print("提交修改")
            cur_szrw.executemany(update,values)
            conn_szrw.commit()
            values=[]
    cur_szrw.executemany(update,values)
    conn_szrw.commit()

#categorynode表同朝代人物去重
def distinctPerson():
    sql="select c_personid from 人物基本表_new where isdelete=0 and gjs_book is not null"
    cur2.execute(sql)
    personids=cur2.fetchall()#有关系且有词条的personid集合
    sql1="select CategoryNodeID from categorynode where parentid in (505263,544017,545731,590902,592159,592430,593301,593692,594364,604875,755745,773026,773716,788660,789061) order by CategoryNodeID"
    cur_szrw.execute(sql1)
    ids=cur_szrw.fetchall()#获取一级朝代对应的CategoryNodeID
    count=0
    deleteIDs=[]
    for i in range(len(ids)):
        begin=ids[i][0]

        if i+1>len(ids):
            break
        end=ids[i+1][0]
        sql2="select CategoryNodeName,count(1) as con from categorynode where CategoryNodeID >="+str(begin)+" and CategoryNodeID <"+str(end)+" and level = 5 group by CategoryNodeName having con>1"
        print(sql2)
        cur_szrw.execute(sql2)
        personsinfo=cur_szrw.fetchall()#获取同一朝代下重复人物的信息
        for personinfo in personsinfo:
            name=personinfo[0]
            sql3="select CategoryNodeID,uuid,entityId,CategoryNodeName from categorynode where CategoryNodeName='"+name+"' and CategoryNodeID >="+str(begin)+" and CategoryNodeID <"+str(end)+" and level = 5 and `Status`=1"
            cur_szrw.execute(sql3)
            persons=cur_szrw.fetchall()#同朝代同名的所有人的CategoryNodeID,uuid和entityId
            entryanduuid=[]#有词条有id的CategoryNodeID集合
            entrynouuid=[]#有词条无id的CategoryNodeID集合
            noentryuuid=[]#无词条有id的CategoryNodeID集合
            noentrynouuid=[]#无词条无id的CategoryNodeID集合
            #print("人物名称："+persons[0][3])
            for person in persons:
                categoryid=str(person[0])
                uuid=person[1]
                entityId=person[2]
                name=person[3]
                if uuid:
                    if entityId:#判断uuid是否有词条且有关系
                        entryanduuid.append(categoryid)
                    else:
                        noentryuuid.append(categoryid)
                else:
                    if entityId:
                        entrynouuid.append(categoryid)
                    else:
                        noentrynouuid.append(categoryid)

            if len(entryanduuid)>1:
                for i in range(1,len(entryanduuid)):
                    deleteIDs.append(entryanduuid[i])
                if len(entrynouuid)>0:
                    for id in entrynouuid:
                        deleteIDs.append(id)
                if len(noentryuuid)>0:
                    for id in noentryuuid:
                        deleteIDs.append(id)
                if len(noentrynouuid)>0:
                    for id in noentrynouuid:
                        deleteIDs.append(id)
            if len(entryanduuid)==1:
                if len(entrynouuid)>0:
                    for id in entrynouuid:
                        deleteIDs.append(id)
                if len(noentryuuid)>0:
                    for id in noentryuuid:
                        deleteIDs.append(id)
                if len(noentrynouuid)>0:
                    for id in noentrynouuid:
                        deleteIDs.append(id)
            if len(entryanduuid)==0:
                if len(entrynouuid)>1:
                    for i in range(1,len(entrynouuid)):
                        deleteIDs.append(entrynouuid[i])
                    if len(noentryuuid)>0:
                        for id in noentryuuid:
                            deleteIDs.append(id)
                    if len(noentrynouuid)>0:
                        for id in noentrynouuid:
                            deleteIDs.append(id)
                if len(entrynouuid)==1:
                    if len(noentryuuid)>0:
                        for id in noentryuuid:
                            deleteIDs.append(id)
                    if len(noentrynouuid)>0:
                        for id in noentrynouuid:
                            deleteIDs.append(id)
                if len(entrynouuid)==0:
                    if len(noentryuuid)>1:
                        for i in range(1,len(noentryuuid)):
                            deleteIDs.append(noentryuuid[i])
                        if len(noentrynouuid)>0:
                            for id in noentrynouuid:
                                deleteIDs.append(id)
                    if len(noentryuuid)==1:
                        if len(noentrynouuid)>0:
                            for id in noentrynouuid:
                                deleteIDs.append(id)
                    if len(noentryuuid)==0:
                        if len(noentrynouuid)>1:
                            for i in range(1,len(noentrynouuid)):
                                deleteIDs.append(noentrynouuid[i])
            if len(deleteIDs)>300:
                delids=','.join(deleteIDs)
                update="update categorynode set `Status`=0 where CategoryNodeID in ("+delids+")"
                print(update)
                cur_szrw.execute(update)
                conn_szrw.commit()
                deleteIDs=[]
    delids=','.join(deleteIDs)
    update="update categorynode set `Status`=0 where CategoryNodeID in ("+delids+")"
    print(update)
    cur_szrw.execute(update)
    conn_szrw.commit()

#将朝代和地点对应信息写入SPACETIMESEARCH
def insertDynastyAndPlace():
    r=open('C:\\Users\\diao\\Documents\\Tencent Files\\415345954\\FileRecv\\地区-朝代.csv',encoding='UTF-8')
    lines=r.readlines()
    map={}
    for i in range(1,len(lines)):#len(lines)
        line=lines[i].replace('\n','')

        cols=line.split(',')
        key=cols[0]+"|"+cols[2]
        value=cols[1]+"|"+cols[3]+"|"+cols[4]
        map[key]=value
    print(len(map))
    count=0
    for key in map:

        place=key.split("|")[0]
        dynasty=key.split("|")[1]
        value=map[key]
        id=value.split("|")[0]
        x=value.split("|")[1]
        y=value.split("|")[2]
        sql="select c_dy from `朝代_简_copy` where c_dynasty_chn='"+dynasty+"'"
        cur2.execute(sql)
        dy=cur2.fetchall()
        entry_id=''
        isdelete=0
        c_dy=dy[0][0]
        sql1="select x_coord,y_coord,isdelete,gjs_entry_id from 地区基本表_简 where c_addr_id="+str(id)
        cur2.execute(sql1)
        p=cur2.fetchall()
        x=p[0][0]
        if x==None:
            x=''
        y=p[0][1]
        if y==None:
            y=''
        isdelete=p[0][2]
        entry_id=p[0][3]
        if entry_id==None:
            entry_id=''
        if x=='':
            sql2="insert into SPACETIMESEARCH(STSNAME,STSTYPE,C_DY,C_DY_NAME,STS_ID,GJS_ENTRY_ID,NEOJ_LABEL,ISDELETE) values(%s,%s,%s,%s,%s,%s,%s,%s)"
            value=(place,'地区',str(c_dy),dynasty,id,entry_id,'humantity_place',str(isdelete))
            print(value)
        else:
            sql2="insert into SPACETIMESEARCH(STSNAME,STSTYPE,C_DY,C_DY_NAME,STS_ID,GJS_ENTRY_ID,NEOJ_LABEL,ISDELETE,X_COORD,Y_COORD) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            value=(place,'地区',str(c_dy),dynasty,id,entry_id,'humantity_place',str(isdelete),str(x),str(y))
            print(value)
        cur_kbase_89.execute(sql2,value)
        conn_kbase_89.commit()
        count=count+1
    print(count)
    r.close()

#为SPACETIMESEARCH添加一级朝代
def addParentDynasty():
    sql="select C_DY,C_DY_NAME,STS_ID  FROM SPACETIMESEARCH  where STSTYPE='地区' and C_DY>0"
    cur_kbase_89.execute(sql)
    datas=cur_kbase_89.fetchall()
    for data in datas:
        c_dy=data[0]
        c_dynasty=data[1]
        stsid=data[2]
        sql1="select c_parentid from `朝代_简_copy` where c_dy="+str(c_dy)
        cur2.execute(sql1)
        parentid=cur2.fetchall()
        if parentid:

            parent_id=parentid[0][0]
            if parent_id==None:
                continue
            else:
                if parent_id==0:#没有上级朝代
                    update="update SPACETIMESEARCH set C_DY_PARENTID=%s,C_DY_PNAME=%s where C_DY=%s and C_DY_NAME=%s and STS_ID=%s"
                    value=(str(c_dy),c_dynasty,str(c_dy),c_dynasty,str(stsid))
                    print(value)
                else:
                    sql2="select c_dynasty_chn from `朝代_简_copy` where c_dy="+str(parent_id)
                    print(sql2)
                    cur2.execute(sql2)
                    dynasty=cur2.fetchall()[0][0]
                    update="update SPACETIMESEARCH set C_DY_PARENTID=%s,C_DY_PNAME=%s where C_DY=%s and C_DY_NAME=%s and STS_ID=%s"
                    value=(str(parent_id),dynasty,str(c_dy),c_dynasty,str(stsid))
                    print(value)
                cur_kbase_89.execute(update,value)
                conn_kbase_89.commit()
        else:
            continue

#插入数据到整理后的事件-地区关系表
def insertRelationEventAndPlace():
    sql="select id,c_addr_id from 事件表 order by id"
    cur2.execute(sql)
    datas=cur2.fetchall()
    values=[]
    pid=0
    for data in datas:
        id=data[0]
        addr_id=data[1]
        pid=pid+1
        if addr_id:
            addr_ids=addr_id.split(';')
            for a_id in addr_ids:
                a_id=int(a_id)
                select = "select sysid from  humantity_place where c_place_id="+str(a_id)
                cur1.execute(select)
                p_sysy_id=cur1.fetchall()[0][0]
                value=(p_sysy_id,pid)
                values.append(value)

                print(str(pid)+"|"+str(p_sysy_id))
        if len(values)>10000:
            insert="insert into relation_event_place(place_sysid,event_sysid) values(%s,%s)"
            cur1.executemany(insert,values)
            conn1.commit()
            print("批量提交")
            values=[]
    insert="insert into relation_event_place(place_sysid,event_sysid) values(%s,%s)"
    cur1.executemany(insert,values)
    conn1.commit()

def insertRelationEventAndDynasty():

    sql="select id,c_dy from 事件表"
    cur2.execute(sql)
    datas=cur2.fetchall()
    values=[]
    for data in datas:
        id=data[0]
        dy=data[1]
        if dy:
            value=(id,dy)
            print(value)
            values.append(value)
    insert = "insert into relation_event_dynasty(event_id,dynasty_id) values(%s,%s)"
    cur1.executemany(insert,values)
    conn1.commit()

def updateoffice():
    sql="select sysno,gjs_entry_id from humentity_office where gjs_bookid is not null and substr(gjs_url,49,17)<>gjs_entry_id "
    cur3.execute(sql)
    datas=cur3.fetchall()
    span=r'(\<.*?\>)'
    span1=r'(\&.*?\;)'
    values=[]
    for data in datas:
        sysno = data[0]
        entry_id=data[1]
        kbase_sql="SELECT AFTER5_ENTRY,BEFORE5_ENTRY,BOOK,BOOK_ID,CODE,ENTRY_11,ENTRY_13,ENTRY_2,ENTRY_CLASS,ENTRY_ID,PARENT_NODE,PUB_DATE,SYS_NVSM_K,SYS_VSM FROM CRFD2008,CRFD2009,CRFD2010,CRFD2011,CRFD2012,CRFD2013,CRFD2014,CRFD2015,CRFD2016,CRFD2017,CRFD2018,CRFD2019,CRFD2020 where  ENTRY_ID='"+entry_id+"'"
        #print(kbase_sql)
        cur_kbase.execute(kbase_sql,bunicode=True)
        info=cur_kbase.fetchall()

        if info:
            onedata=info[0]
            gjs_bookid=onedata[3]
            gjs_book=onedata[2]
            gjs_name=onedata[5]
            gjs_txt=onedata[7].strip()
            de=re.findall(span,gjs_txt)
            if de:
                for i in range(len(de)):
                    gjs_txt=gjs_txt.replace(de[i],'')
            gjs_txt=gjs_txt.replace('<','')
            gjs_time=onedata[11]
            gjs_keyword=onedata[12]
            gjs_vsm=onedata[13]
            gjs_entry_id=entry_id
            gjs_url="https://gongjushu.cnki.net/RBook/Detail?entryId="+gjs_entry_id
            gjs_before=onedata[1]
            de1=re.findall(span1,gjs_before)
            if de1:
                gjs_before=gjs_before.replace(de1[0],'')
            de2=re.findall(span,gjs_before)
            if de2:
                for i in range(len(de2)):
                    gjs_before=gjs_before.replace(de2[i],'')
            gjs_after=onedata[0]
            gjs_code=onedata[4]
            gjs_parent_node=onedata[10]
            gjs_entry_class=onedata[8]
            gjs_person_relation=onedata[0]
            gjs_addr_relation=onedata[0]
            gjs_time_relation=onedata[0]
            gjs_org_relation=onedata[0]
            gjs_dy_relation=onedata[0]
            gjs_count=len(info)
            gjs_entries=onedata[0]
            update="update humentity_office set gjs_bookid=%s,gjs_book=%s,gjs_name=%s,gjs_txt=%s,gjs_time=%s,gjs_keyword=%s,gjs_vsm=%s,gjs_url=%s,gjs_before=%s,gjs_after=%s,gjs_code=%s,gjs_parent_node=%s,gjs_entry_class=%s where sysno =%s "
            value=(gjs_bookid,gjs_book,gjs_name,gjs_txt,gjs_time,gjs_keyword,gjs_vsm,gjs_url,gjs_before,gjs_after,gjs_code,gjs_parent_node,gjs_entry_class,sysno)
            values.append(value)
            #print(value)
            if len(values)>3000:
                cur3.executemany(update,values)
                conn3.commit()
                print("批量提交")
                values=[]
    update="update humentity_office set gjs_bookid=%s,gjs_book=%s,gjs_name=%s,gjs_txt=%s,gjs_time=%s,gjs_keyword=%s,gjs_vsm=%s,gjs_url=%s,gjs_before=%s,gjs_after=%s,gjs_code=%s,gjs_parent_node=%s,gjs_entry_class=%s where sysno =%s  "
    cur3.executemany(update,values)
    conn3.commit()
#更新人物表的工具书信息
def updatepersons():
    sql="select tts_sysno,gjs_entry_id from humantity_persons where gjs_bookid is not null and substr(gjs_url,49,17)<>gjs_entry_id "
    cur3.execute(sql)
    datas=cur3.fetchall()
    span=r'(\<.*?\>)'
    span1=r'(\&.*?\;)'
    values=[]
    for data in datas:
        try:
            sysno = data[0]
            entry_id=data[1]
            kbase_sql="SELECT AFTER5_ENTRY,BEFORE5_ENTRY,BOOK,BOOK_ID,CODE,ENTRY_11,ENTRY_13,ENTRY_2,ENTRY_CLASS,ENTRY_ID,PARENT_NODE,PUB_DATE,SYS_NVSM_K,SYS_VSM FROM CRFD2008,CRFD2009,CRFD2010,CRFD2011,CRFD2012,CRFD2013,CRFD2014,CRFD2015,CRFD2016,CRFD2017,CRFD2018,CRFD2019,CRFD2020 where  ENTRY_ID='"+entry_id+"'"
            #print(kbase_sql)
            cur_kbase.execute(kbase_sql,bunicode=True)
            info=cur_kbase.fetchall()

            if info:
                onedata=info[0]
                gjs_bookid=onedata[3]
                gjs_book=onedata[2]
                gjs_name=onedata[5]
                gjs_txt=onedata[7].strip()
                de=re.findall(span,gjs_txt)
                if de:
                    for i in range(len(de)):
                        gjs_txt=gjs_txt.replace(de[i],'')
                gjs_txt=gjs_txt.replace('<','')
                gjs_time=onedata[11]
                gjs_keyword=onedata[12]
                gjs_vsm=onedata[13]
                gjs_entry_id=entry_id
                gjs_url="https://gongjushu.cnki.net/RBook/Detail?entryId="+gjs_entry_id
                gjs_before=onedata[1]
                de1=re.findall(span1,gjs_before)
                if de1:
                    gjs_before=gjs_before.replace(de1[0],'')
                de2=re.findall(span,gjs_before)
                if de2:
                    for i in range(len(de2)):
                        gjs_before=gjs_before.replace(de2[i],'')
                gjs_after=onedata[0]
                gjs_code=onedata[4]
                gjs_parent_node=onedata[10]
                gjs_entry_class=onedata[8]
                gjs_person_relation=onedata[0]
                gjs_addr_relation=onedata[0]
                gjs_time_relation=onedata[0]
                gjs_org_relation=onedata[0]
                gjs_dy_relation=onedata[0]
                gjs_count=len(info)
                gjs_entries=onedata[0]
                update="update humantity_persons set gjs_bookid=%s,gjs_book=%s,gjs_name=%s,gjs_txt=%s,gjs_time=%s,gjs_keyword=%s,gjs_vsm=%s,gjs_url=%s,gjs_before=%s,gjs_after=%s,gjs_code=%s,gjs_parent_node=%s,gjs_entry_class=%s where tts_sysno =%s "
                value=(gjs_bookid,gjs_book,gjs_name,gjs_txt,gjs_time,gjs_keyword,gjs_vsm,gjs_url,gjs_before,gjs_after,gjs_code,gjs_parent_node,gjs_entry_class,sysno)
                values.append(value)
                #print(value)
                if len(values)>100:
                    cur3.executemany(update,values)
                    conn3.commit()
                    print("批量提交")
                    values=[]
        except Exception:
            traceback.print_exc()
            print(sysno)
            print(len(gjs_txt))
            continue
    update="update humantity_persons set gjs_bookid=%s,gjs_book=%s,gjs_name=%s,gjs_txt=%s,gjs_time=%s,gjs_keyword=%s,gjs_vsm=%s,gjs_url=%s,gjs_before=%s,gjs_after=%s,gjs_code=%s,gjs_parent_node=%s,gjs_entry_class=%s where tts_sysno =%s  "
    cur3.executemany(update,values)
    conn3.commit()

#去除kbase工具书表Name字段的标签
def adjustGjsBookName():
    tables=['humantity_event']#,'WX_PERSON'
    shu=['一、','二、','三、','四、','五、','六、','七、','八、','九、','十、','十一、','十二、','十三、','十四、','十五、','十六、','十七、','十八、','十九、','二十、','二十一、','二十二、','三十五、']
    zi=['一　','二  ','三　','四　','五　','六　','七　','八　','九　','十　','十一　','十二　','十三　','十四　','十五　','十六　','十七　','十八　','十九　','二十　']
    shuzi=['一 ','二 ','三 ','四 ','五 ','六 ','七 ','八 ','九 ','十 ','十一 ','十二 ','十三 ','十四 ','十五 ','十六 ','十七 ','十八 ','十九 ','二十 ']
    span=r'(\<.*?\>)'
    span1=r'>(.*?)<'
    begin=r'^[0-9]{1,}\.' #匹配所有数字开头，后面一个'.'的
    begin1=r'[0-9]$'
    begin2=r'[0-9]{1,}月[0-9]{1,}日'
    begin3=r'(^\(.*\))'
    begin4=r'^[0-9]{1,}\、'
    begin5=r'^[0-9]{1,}'
    begin6=r'^[0-9]{1,}\.[0-9]{1,}'
    begin7=r'(^\（.*\）)'
    begin8=r'^[A-Z]\、'
    for table in tables:
        sql="select id,gjs_name from "+table+" where gjs_name like '%<%' or gjs_name like '%、%'"
        print(sql)
        cur3.execute(sql)
        datas= cur3.fetchall()
        print(len(datas))
        for data in datas:
            id=data[0]
            name=data[1]
            for s in shu:
                if name.startswith(s):
                    name=name.replace(s,'').replace(' ','')
            for s in zi:
                if name.startswith(s):
                    name=name.replace(s,'').replace(' ','')
            for s in shuzi:
                if name.startswith(s):
                    name=name.replace(s,'').replace(' ','')
            de=re.findall(begin,name)
            if de:
                name=name.replace(de[0],'').replace(' ','')
            de1=re.findall(begin1,name)
            if de1:
                name=name.replace(de1[0],'').replace(' ','')
            de2=re.findall(begin2,name)
            if de2:
                name=name.replace(de2[0],'').replace(' ','')
            de3=re.findall(begin3,name)
            if de3:
                name=name.replace(de3[0],'').replace(' ','')
            de4=re.findall(begin4,name)
            if de4:
                name=name.replace(de4[0],'').replace(' ','')
            de6=re.findall(begin6,name)
            if de6:
                name=name.replace(de6[0],'').replace(' ','')
            de5=re.findall(begin5,name)
            if de5:
                name=name.replace(de5[0],'').replace(' ','')
            de7=re.findall(begin7,name)
            if de7:
                name=name.replace(de7[0],'').replace(' ','')
            de8=re.findall(begin8,name)
            if de8:
                name=name.replace(de8[0],'').replace(' ','')
            if '<' in name:
                de1=re.findall(span1,name)
                if de1:
                    for i in range(len(de1)):
                        name=name.replace(de1[i],'')
                de=re.findall(span,name)
                if de:
                    for i in range(len(de)):
                        name=name.replace(de[i],'')
            if ' ' in name:
                names=name.split(' ',1)
                if '第' in names[0]:
                    name=name.replace(names[0],'').replace(' ','')
            if '　' in name:
                names=name.split('　',1)
                if '第' in names[0]:
                    name=name.replace(names[0],'').replace('　','')
            if '一○、' in name:
                name=name.replace('一○、','')
            if '附:' in name:
                name=name.replace('附:','')
            name=name.replace('　','').replace('\n','').replace(' ','')
            #print(name1)
            print(name)
            update="update humantity_event set gjs_name =%s where id=%s"
            value=(name,id)
            # print(value)
            cur3.execute(update,value)
            conn3.commit()

#给categorynode表的官职添加拼音
def addpinyin():
    sql="select sysno,c_office_type_desc_chn from humentity_office  "
    cur3.execute(sql)
    datas=cur3.fetchall()
    pattern=r'[\u4e00-\u9fa5]'
    values=[]
    for data in datas:
        id=data[0]
        name=data[1]
        chinese=re.findall(pattern,name)
        if chinese:
            pinyin=chinese[0]
            pinyin=lazy_pinyin(pinyin)[0][:1].capitalize()

            value=(pinyin,id)
            values.append(value)
            if len(values)>500:
                update="update humentity_office set c_office_name=%s where sysno=%s"
                cur3.executemany(update,values)
                conn3.commit()
                print("批量提交")
                values=[]
    update="update humentity_office set c_office_name=%s where sysno=%s"
    cur3.executemany(update,values)
    conn3.commit()

def updatetime():
    sql = "select id,时间 from `学会协会` where 时间 like '%年%'"
    cur1.execute(sql)
    datas=cur1.fetchall()
    for data in datas:
        id=data[0]
        time=data[1]
        if '～' in time:
            time=time.split('～')[0].replace(' ','')
        if '—' in time:
            time=time.split('—')[0].replace(' ','')
        if '~' in time:
            time=time.split('~')[0].replace(' ','')

        time=time.replace('年','-').replace('月','-').replace('日','')
        times=time.split('-')
        tt=[]
        for t in times:
            if len(t)>1:
                tt.append(t)
            if len(t)==1:
                t='0'+t
                tt.append(t)
        time='-'.join(tt)
        update = "update `学会协会` set `时间`='"+time+"' where id="+str(id)
        print(update)
        cur1.execute(update)
        conn1.commit()

def aaaa():
    sql1="select id from 事件表 "
    cur2.execute(sql1)
    ids=cur2.fetchall()
    d1=[]
    d2=[]
    for id in ids:
        d1.append(id[0])
    sql2="select id from humantity_event"
    cur3.execute(sql2)
    ids1=cur3.fetchall()
    for id in ids1:
        d2.append(id[0])
    for id in d1:
        if id not in d2:
            print(id)

if __name__ == '__main__':
    try:
        conn1 = pymysql.Connect(host = '10.170.128.56',
                                port = 3306,
                                user = 'root',
                                passwd = '123456',
                                db = 'kexie',
                                charset='utf8mb4')
        cur1 = conn1.cursor()
        conn2 = pymysql.Connect(host = '10.170.128.56',
                                port = 3306,
                                user = 'root',
                                passwd = '123456',
                                db = 'cnki_humantity_simple',
                                charset='utf8mb4')
        cur2 = conn2.cursor()
        conn3 = pymysql.Connect(host = '10.170.128.56',
                                port = 3306,
                                user = 'root',
                                passwd = '123456',
                                db = 'humantity_nlp',
                                charset='utf8mb4')
        cur3 = conn3.cursor()
        conn_kbase = KBase.connect(host="192.168.100.12",
                                   port=4567,
                                   user="DATAANALYSIS",
                                   passwd="DATAANALYSIS123.")
        cur_kbase = conn_kbase.cursor()
        conn_kbase_89 = KBase.connect(host="192.168.105.89",
                                   port=4567,
                                   user="DBOWN",
                                   passwd="")
        cur_kbase_89 = conn_kbase_89.cursor()
        conn_szrw = pymysql.Connect(host = '10.170.128.56',
                                port = 3306,
                                user = 'root',
                                passwd = '123456',
                                db = 'szrwdev',
                                charset='utf8mb4')
        cur_szrw = conn_szrw.cursor()
        #tables=['文学部']
        # for table in tables:
        #     adjustTable(table)
        #     getPersonInfo2(table)
        # for dynasty in dynastys:
        #     getOfficeByDynasty(dynasty)
        #getOfficeBook()
        insertintospace()

    finally:
        cur1.close()
        conn1.close()
        cur2.close()
        conn2.close()
        cur3.close()
        conn3.close()
        cur_kbase.close()
        conn_kbase.close()
        cur_kbase_89.close()
        conn_kbase_89.close()
        cur_szrw.close()
        conn_szrw.close()




